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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,147
Office Version
  1. 365
  2. 2016
Platform
  1. 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.

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

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,748
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 14, 2018
Messages
2,147
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for that, that worked perfectly!! How did you do it? I didn't see any link.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,748
Office Version
  1. 365
Platform
  1. Windows
Sorry about that, forgot to attach the link. See below.

 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,329
Members
415,892
Latest member
ChloeLM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top