Forum Discussion
Calculate total working hours between date/time excluding weekends and break time
Hello,
I want to calculate the hours between two fields, but I just want a simple number of hours. No concatenated labels, not days, just accumulated hours and I need it to exclude weekends and break time. I've searched in this forum but I couldn't find suitable formula, forgive me if this has been discussed before.
Example:
The working hours start from 08:00 to 17:00 from Monday to Friday
07/12/24 9:00 - 07/15/24 15:40 (minus weekends and break time between 12:00 to 13:00) = # of hours
The result should be 13:40
I attached the wrong version, sorry.
The 2 hours in the first example is because the 1st of May is in the holiday list. If you remove that date from the holiday list, you get 3 hours.
See the attached version.
Seems I am a bit late to the party!
This is an Excel 365 solutions that will produce a table of shifts worked or the simple total (default).
The worksheet formula is
= HoursWorkedλ(timeline, start, end, holidays)The timeline I defined to be
timeline = SEQUENCE(14,1,DATE(2024,7,8))though I could have used a shorter interval by basing it on the work start and end. The complexity is all hidden away in the Lambda function
HoursWorkedλ = LAMBDA(timeline, start, end, [holidays], [crosstab?], LET( shiftTimes, TIME({8,12,13,17},0,0), activeShifts, {1, 0, 1, 0}, activeDays, NETWORKDAYS(+timeline, +timeline, holidays), shiftChange, TOCOL(timeline + shiftTimes), intervalStart, SORT(VSTACK(shiftChange, start)), intervalEnds, SORT(VSTACK(shiftChange, end)), intervalDuration, DROP(intervalEnds - intervalStart, 1), workedDurations, activeShifts * activeDays * WRAPROWS(intervalDuration, 4), IF(crosstab?, workedDurations, SUM(workedDurations)) ) )- Hi thanks for replying, i tried to change the input but it shows error #NAME?.
There aren't many defined names so entering
= timeline = start = end = holidaysshould reveal which one is missing, though 'holidays' could be dropped from the formula. Alternatively, the function HoursWorkedλ is itself a defined name and could be missing if you are using another workbook. Copying cell D4 and pasting it to the other workbook should bring the function across, The function is written using English localisation, which could cause problems if they are not translated properly.
I simplified my formula to make it self-contained and to provide a closer match to HansVogelaar's VBA solution
Worksheet formula = MAP(Start, End, HoursWorkedλ) HoursWorkedλ = LAMBDA(start, end, LET( timeline, SEQUENCE(1 + INT(end) - INT(start), 1, INT(start)), shiftTimes, TIME({8,12,13,17},0,0), activeShifts, {1, 0, 1, 0}, activeDays, NETWORKDAYS(+timeline, +timeline, holidays), shiftChange, TOCOL(timeline + shiftTimes), intervalStart, SORT(VSTACK(shiftChange, start)), intervalEnds, SORT(VSTACK(shiftChange, end)), intervalDuration, DROP(intervalEnds - intervalStart, 1), workedDurations, activeShifts * activeDays * WRAPROWS(intervalDuration, 4), SUM(workedDurations) ) )PeterBartholomew1 Hi, idk why, it keeps error if i change the input. But I can now use HansVogelaar vba function and it works really fine. Thanks for helping me.
The problem would appear to be that you are not using Excel 365 or 2021, so dynamic arrays, and the MAP lambda helper function in particular, are not available to you.
With legacy versions of Excel (a nicer way of saying 'obsolete'), VBA is your best way forward. That probably applies to your other issue of converting comma separated lists to arrays, although there options such as Power Query or manual processing such as Text to Columns exist.
Here is a solution using a custom VBA function.
HansVogelaarHi thanks for responding, I changed the input but the result is still wrong, and it can't exclude lunch break.
I attached the wrong version, sorry.
The 2 hours in the first example is because the 1st of May is in the holiday list. If you remove that date from the holiday list, you get 3 hours.
See the attached version.
As variant to HansVogelaar 's solution, I've created a formula assuming a couple of things.
I would like to understand the purpose or logic behind this calculation but this is how I structured it based on your definition of the problem: I assumed the start date and the end date could have different start and end times as the official ones. But not the dates in between.
That is, if we are analyzing from 07/12/2024 to 07/16/2024, my formula will take into account how many worked hours are within each of these exact two dates (could be 4 hours on 07/12 and 6:30 hours on 07/16) by looking at your specified entries. However, the calculated hours in the workdays in between which, in this case would be 07/15/2024, will always be 8:00.
A bit of a messy formula but if my logic was slightly going in the right direction it could also serve. Not a very accurate understanding of how working hours are calculated or revised, but it could be useful to you.
Martin_Angosto hi thanks for replying, I changed the input into these but still got the wrong result
VladLockhart My issue is with Excel spreadsheet database which is separated by commas and I would like to revert back to a simple column and cell based view ? How c
an I do that ?
