Number of days to not include the start date and the end date

[dpaton05]

I have 2 cells that have a start and an end date in them.

• B8 is the start date
• B9 is the end date
• I have a list of public holiday dates in the range Sheet2!G87:DO97.

I then have some formulas for calculating the number of weekdays, saturdays, sundays and public holidays in the time period.

• The formula to calculate the weekdays in the period between B8 and B9 is =NETWORKDAYS.INTL(B8,B9,1,Sheet2!G87:DO97)
• The formula to calculate the satudays is =INT((WEEKDAY($B$8- 7)-$B$8+$B9)/7)
• The formula to calculate the sundays is =INT((WEEKDAY($B$8- 1)-$B$8+$B9)/7)
• The formula to calculate the public holidays is =SUMPRODUCT((Sheet2!G87:DO97>=B8)*(Sheet2!G87:DO97<=B9))

I need the formulas to not count the start and the end date as at the moment, the formulas include the start and end dates.

Can someone help me adjust the formulas so they do not count the start or end dates in the calculation please?

 

[Misca]

The easiest way would be to use StartDate+1 and EndDate-1 in your formulas. I'd use WORKDAY to shift the dates by one but using that would mess up your Sat + Sun calculations.

Not sure what you want to see in cases where the StartDate & EndDate happens to be the same date or following workdays but I'd wrap the original workday calculations in a MAX just to make sure the duration is always at least 1.

 

[dpaton05]

Thanks for the reply, I have no idea as to how to write these formulas so any help would be appreciated. I just need them to calculate if there are any of the specific dates in the period, such as weekdays etc. If the start and end date are the same day, the cost will take into account thestart and end times and calculate from there.

 

[Misca]

If your dates include the time as well you could try something like

=MAX(NETWORKDAYS.INTL(A1+1,B1-1,1,Holidays),0)+MOD(B1-A1,1)

Where
A1 = calculation Start DateTime
B1 = calculation End DateTime and
Holidays = the range where you have the holidays ie. dates excluded from the workdays calculations.

The formula returns the total number of hours on workdays (parameter 1 treats Saturday and Sunday as the weekend) between those two time stamps.

If you want to calculate only the working hours (ie. each full workday is calculated as X hours) you might want to multiply the the MAX-part of the formula with your X. Or split the formula into two separate cells.