• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The formula for calculating the number of days spent by an employee in a given country during each month

Hany ali

Active Member
hello every body
i want to help me to found formula for this problem to get and count Total days for any employee from some country in some monthes from start date till end date from this Country
thanks for this Help
 

Attachments

  • Untitled.png
    Untitled.png
    37.9 KB · Views: 11
  • place Date For Employee.xlsx
    10.9 KB · Views: 4
I'd recommend giving more than just a single line of data. As well, I'd also recommend restructuring your data and flattening out.

There is no need to alter the source table, but it would make your life much easier if you had intermediate table for the purpose of calculation.

Exact steps will vary based on your Excel version. Please let us know which version of Excel that you are using.
 
i have excel 2010 & 2016
Next Line Just For Explain the Result which i want
i use just one line ,i want This Result from range J3 : AC3
Thanks alot mr. Chihiro For Your Fast Reply
 
Last edited:
But you only have Egypt, Tunisia, Canada. No Japan in your employee data.

As well, what happens if the person travels more than once to same location in a given year?

At any rate, I'd recommend structuring your data like below.

64212
 
thanks ,i know about japan
but can be solve this problem by my format from excel sheet -because it's fixed from our system
 
thanks ,i know about japan

Ok, but you haven't answered the question...

If there is Japan added, what happens to the data structure? What if there's more than 1 travel/stay to same country in a given year? How would that be reflected on your data?
 
no problem for this
it should be to add this stay for the month in this country as you see in my photo which I Sent Before
because you know i want to collect this stay by month in certain country
 
i found this
Code:
=SUMPRODUCT(--(MONTH(ROW(INDIRECT(C3&":"&IF(D3="",TODAY(),D3))))=MONTH($J1))*(YEAR(ROW(INDIRECT(C3&":"&IF(D3="",TODAY(),D3))))=YEAR($J1)))
but without Select country
Please I Want by Select country in formula
thanks for all
 
64219

Try..................

In J3, formula copied across to AC3

=IFERROR(MAX(0,MIN(EOMONTH(0+(1&LOOKUP("zz",$J$1:J$1)),0),INDEX($C$3:$H$3,MATCH(J$2,$C$2:$H$2,0)+1))-MAX(0+(1&LOOKUP("zz",$J$1:J$1)),INDEX($C$3:$H$3,MATCH(J$2,$C$2:$H$2,0)))+1),0)

Regards
Bosco
 

Attachments

  • Place Date For Employee(BY).xlsx
    13 KB · Views: 7
Back
Top