Formula to calculate number of days in period not including the start and the end date

[dpaton05]

I need to calculate the cost of a service over a period of days. The cost is broken down over a 24 hour period with different rates for different times on the day. I have worked out how much a full, 24 hour period will cost. The start and the end date will not be a full day cost as there will be a start and end time.

• B8 has the start date
• B9 has the end date
• E8 has the start time
• E9 has the end time
• Public holidays are stored in the range Sheet2!G87:DO97

• H6 =B8+1
• J6 =B9-1

Therefore H6 and J6 are the start and end dates that are full days.

I want to work out how many weekdays, saturdays, sundays and public holidays are in the range H6 to J6.

These are the formulas I have but they do not appear to be correct.
Number of weekdays =NETWORKDAYS.INTL(H6+1,J6-1,1,Sheet2!G87:DO97)
Number of saturdays =INT((WEEKDAY($H$6- 7)-$H$6+$J6)/7)
Number of sundays =INT((WEEKDAY($H$6- 1)-$H$6+$J6)/7)
Number of public holidays =SUMPRODUCT((Sheet2!G87:DO97>=H6)*(Sheet2!G87:DO97<=J6))

I don't understand how these formulas work so could someone please help me with any problems the above formula may have?

I think it is the weekday formula that I am having problems with as the others appear to work, I think.

 

[AhoyNC]

See if this is what you want.
See this link to help on NETWORKDAYS.INTL which should help explain the formula below.

Book1
	A	B					G	H	I
6							Start	11/26/2019
7							End	12/15/2019
8	Start	11/25/2019
9	End	12/16/2019						Number of :	Count
10								Weekdays	14
11								Sat	3
12								Sun	3
Sheet1

Cell Formulas
Range	Formula
H6	H6	=B8+1
H7	H7	=B9-1
I10	I10	=NETWORKDAYS.INTL(H6,H7,"0000011")
I11	I11	=NETWORKDAYS.INTL(H6,H7,"1111101")
I12	I12	=NETWORKDAYS.INTL(H6,H7,"1111110")

 

[dpaton05]

Thanks for that, that worked perfectly!! How did you do it? I didn't see any link.

 

[AhoyNC]

Sorry about that, forgot to attach the link. See below.

NETWORKDAYS.INTL function - Microsoft Support

NETWORKDAYS.INTL returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

support.office.com

 

[dpaton05]

Cheers