• 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.

Count days between two dates that fall between two other dates.

Stefan Teuthof

Stefan Teuthof

New Member
Hi,
I have to count the days between two dates, which I can do. But I need to count only the days that fall between those dates but only if they fall between another pay period date range.

Example in first row "Employee 1" if I have a staff member on leave from 25/11/19 till 05/12/19 but I need to count how many days between that range but only if they fall between pay period 02/12/19 - 08/12/19 (Inclusive) I would expect a result of 5 days with the correct formula.

I have tried lots of different formulas and just cant get the result I need

Can anyone help please?

Thanks in advance

64446
 
Thanks very much for taking the time @vletm

I received some help from someone else as well yesterday.

The solution they provided seemed to do the trick, the times were not important and can be ignored (Sorry I should have mentioned that)

Thought I would share as it might help others in the future

B2 =SUMPRODUCT(((ROW(INDIRECT($AB$1&":"&$AC$1)))>=INT($H2))*(((ROW(INDIRECT($AB$1&":"&$AC$1)))<=INT($I2))))

I will have a play with your formula as well and keep it for future reference.

Thanks again
 
Stefan Teuthof
Seems that You would reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
There are writings about Cross-Posting ... which You should have mentioned too.
 
Thanks for pointing that out. It certainly wasn't intentional.

I hadn't read the rules about cross posting so me "mentioning that too" wouldn't have been possible because I didn't realise.

I wouldn't have replied to say thank you and mention it if I had realised it was against the rules.

Anyway I am aware of the rules now and will keep that in mind for future posts.
 
Stefan Teuthof
There are rules everywhere
... basically written about common sense eg Thank You.
The cross-posting is not against the rules!
... but there are actions, which should do - if someone uses it.
 
There are two factors that complicate formulas for overlapping time periods such as this.
The basic
= MIN(end) - MAX(start)
goes wrong if the periods do not intersect.

A more complicated but generic formula is obtained if each date defining the period worked (as a 2-cell row) is compared with the dates defining the pay period (as a 2-cell column). Taking the minimum of each pair-wise comparison gives a 2x2 array. Subtracting the first row from the second and first column from the second gives the result.
= SUM( IF(periodWorked<payPeriod, periodWorked, payPeriod) * {1,-1;-1,1} )

The second complication is that a start date assumes the start of the day whereas an end date is usually taken to be the end of the day. For example, in the case where the two dates are the same, the period worked is 1, not 0. The end date needed is the first rest day; similarly the pay period is defined by the first day of the next pay period.
 
vletm said:
Stefan Teuthof
There are rules everywhere
... basically written about common sense eg Thank You.
The cross-posting is not against the rules!
... but there are actions, which should do - if someone uses it.
Click to expand...
Not exactly sure what you mean anymore sorry, I did say thank you at the start and i do appreciate your help. Anyway I'm going to move forward. Thanks
 
Peter Bartholomew said:
There are two factors that complicate formulas for overlapping time periods such as this.
The basic
= MIN(end) - MAX(start)
goes wrong if the periods do not intersect.

A more complicated but generic formula is obtained if each date defining the period worked (as a 2-cell row) is compared with the dates defining the pay period (as a 2-cell column). Taking the minimum of each pair-wise comparison gives a 2x2 array. Subtracting the first row from the second and first column from the second gives the result.
= SUM( IF(periodWorked<payPeriod, periodWorked, payPeriod) * {1,-1;-1,1} )

The second complication is that a start date assumes the start of the day whereas an end date is usually taken to be the end of the day. For example, in the case where the two dates are the same, the period worked is 1, not 0. The end date needed is the first rest day; similarly the pay period is defined by the first day of the next pay period.
Click to expand...
Thank you for this Peter
 
Cross-posting.
As I see it, the relevant points are
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Other than that, don't feel bad about the exchange. Just continue contributing questions and perhaps, in due course, answers!
 
Peter Bartholomew said:
Cross-posting.
As I see it, the relevant points are
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Other than that, don't feel bad about the exchange. Just continue contributing questions and perhaps, in due course, answers!
Click to expand...
Thanks Peter
 
You must log in or register to reply here.
Back
Top