dpaton05
Wellknown Member
 Joined
 Aug 14, 2018
 Messages
 2,147
 Office Version

 365
 2016
 Platform

 Windows
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.
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,J61,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.
 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 =B91
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,J61,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.
Last edited: