# 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

• 37.9 KB Views: 11
• 10.9 KB Views: 4

#### Chihiro

##### Excel Ninja
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.

#### Hany ali

##### Active Member
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:

#### Chihiro

##### Excel Ninja
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.

#### Hany ali

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

#### Chihiro

##### Excel Ninja
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?

#### Hany ali

##### Active Member
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

#### Hany ali

##### Active Member
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

#### bosco_yip

##### Excel Ninja

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

• 13 KB Views: 7

#### Hany ali

##### Active Member
Exactly this is required
Really you are a masterful and wonderful teacher - thank you very much