Feb 18 2023 04:04 AM
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
Feb 18 2023 04:15 AM - edited Feb 18 2023 04:16 AM
Do you want to include or exclude weekend days?
And how do you want to count partial days, such as 29.01.2023 in your example (up to 13:30 instead of 16:00)?
Feb 18 2023 04:21 AM
Thank you for your reply.
R.e. weekends I want to include these.
R.e. partial days I'd like to count these as a part of a day e.g. 08:00 to 13:30 would be 0.6875 days.
Feb 18 2023 04:50 AM
SolutionWith 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)
Feb 19 2023 03:19 AM
Mar 03 2023 01:16 AM
@Hans Vogelaar I've had to move some cells and the start date is now in cell X1 and the end date in cell Y1. I've tried changing A1 for X1, and B1 for Y1 in the formula but the error "#NAME?" comes up. Do you know why this may be?
Mar 03 2023 04:21 AM
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?
Mar 03 2023 06:06 AM
Mar 03 2023 06:16 AM
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)
Mar 03 2023 07:18 AM