Forum Discussion
Calculate number of 8 hour days a date range
- Feb 18, 2023
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)
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.
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)
- B-unitMar 03, 2023Copper Contributor
HansVogelaar 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?
- HansVogelaarMar 03, 2023MVP
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-unitMar 03, 2023Copper ContributorAh 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?
- B-unitFeb 19, 2023Copper Contributor