Forum Discussion
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
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)
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)
