• 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

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

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.

64212
 

Hany ali

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

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

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
 
Top