Forum Discussion

Copper Contributor

Feb 18, 2023

Solved

Calculate number of 8 hour days a date range

Hi, I'm looking to calculate how many days 8 hour days (08:00 - 16:00) there are in a defined period i.e. 20.01.2023, 06:30 to 29.01.2023, 13:30. This is a relativly small period so I can manually

Formulas and Functions

HansVogelaar
Feb 18, 2023
B-unit

With the start date/time in A1 and the end date/time in B1:

=LET(startdate, A1, enddate, B1, wholedays, INT(enddate-startdate), newstart, startdate+wholedays, starttime, MIN(MAX(MOD(newstart, 1), TIME(8, 0, 0)), TIME(16, 0, 0)), endtime, MAX(MIN(MOD(enddate, 1), TIME(16, 0, 0)), TIME(8, 0, 0)), partday, MOD(endtime-starttime, TIME(8, 0, 0))/TIME(8,0,0), wholedays+partday)

HansVogelaar

MVP

Mar 03, 2023

B-unit

This doesn't work?

=LET(startdate, X1, enddate, Y1, wholedays, INT(enddate-startdate), newstart, startdate+wholedays, starttime, MIN(MAX(MOD(newstart, 1), TIME(8, 0, 0)), TIME(16, 0, 0)), endtime, MAX(MIN(MOD(enddate, 1), TIME(16, 0, 0)), TIME(8, 0, 0)), partday, MOD(endtime-starttime, TIME(8, 0, 0))/TIME(8,0,0), wholedays+partday)

Are you using a different version of Excel than before?

B-unit

Copper Contributor

Mar 03, 2023

Ah maybe. I save the document on my one drive and open it on different machines. one is Excel 2016 and one is Excel for Microsoft 365 MSO. Will this make a differance?

HansVogelaar
MVP
Mar 03, 2023
B-unit

Yes: the LET function is only available in Excel in Microsoft 365 and Office 2021, not in Office 2019 or earlier versions.

Try this instead:

=INT(Y1-X1)+MOD(MAX(MIN(MOD(Y1, 1), TIME(16, 0, 0)), TIME(8, 0, 0))-MIN(MAX(MOD(X1+INT(Y1-X1), 1), TIME(8, 0, 0)), TIME(16, 0, 0)), TIME(8, 0, 0))/TIME(8,0,0)
- B-unit
  Copper Contributor
  Mar 03, 2023
  That's brilliant - it works fine. Thank you very much.