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

#### dpaton05

##### Well-known Member
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.

Last edited:

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### AhoyNC

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

Book1
ABGHI
6Start11/26/2019
7End12/15/2019
8Start11/25/2019
9End12/16/2019Number of :Count
10Weekdays14
11Sat3
12Sun3
Sheet1
Cell Formulas
RangeFormula
H6H6=B8+1
H7H7=B9-1
I10I10=NETWORKDAYS.INTL(H6,H7,"0000011")
I11I11=NETWORKDAYS.INTL(H6,H7,"1111101")
I12I12=NETWORKDAYS.INTL(H6,H7,"1111110")

#### dpaton05

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

Cheers

Replies
3
Views
164
Replies
1
Views
73
Replies
4
Views
87
Replies
3
Views
118
Replies
2
Views
277

1,127,494
Messages
5,625,083
Members
416,070
Latest member
Austen G

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back