SOLVED

# Calculate total working hours between date/time excluding weekends and break time

Copper Contributor

# 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

21 Replies

# Re: Calculate total working hours between date/time excluding weekends and break time

Here is a solution using a custom VBA function.

# Re: Calculate total working hours between date/time excluding weekends and break time

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.

# Re: Calculate total working hours between date/time excluding weekends and break time

@HansVogelaarHi thanks for responding, I changed the input but the result is still wrong, and it can't exclude lunch break.

# Re: Calculate total working hours between date/time excluding weekends and break time

@Martin_Angosto hi thanks for replying, I changed the input into these but still got the wrong result

best response confirmed by VladLockhart (Copper Contributor)
Solution

# Re: Calculate total working hours between date/time excluding weekends and break time

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.

# Re: Calculate total working hours between date/time excluding weekends and break time

@HansVogelaar thanks dude it seems working just fine. But I can't copy it to my existing excel data. It always shows #NAME? can u help with that?

# Re: Calculate total working hours between date/time excluding weekends and break time

Open both my sample workbook and your workbook.

Press Alt+F11 to activate the Visual Basic Editor.

In the Project Explorer pane on the left hand side, expand TimeDifferenceWithBreak.xlsm, and Modules under it if necessary.

Drag Module1 to your workbook and drop it there.

Switch back to Excel.

Save your workbook as a macro-enabled workbook (*.xlsm)

# Re: Calculate total working hours between date/time excluding weekends and break time

Hi thanks, I did as u told me. But how do I use it, how do I call function on the column i want? I'm really sorry I'm new to this.

# Re: Calculate total working hours between date/time excluding weekends and break time

Create a list of public holidays.

Select the list, and name it Holidays.

Now let's say you have a start date/time in D2 and the corresponding end date/time in E2.

In another cell, for example E2, enter the formula =TimeDiff(D2, E2)

This can be filled down if required.

# Re: Calculate total working hours between date/time excluding weekends and break time

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))
)
)``````

# Re: Calculate total working hours between date/time excluding weekends and break time

Hi thanks for replying, i tried to change the input but it shows error #NAME?.

# Re: Calculate total working hours between date/time excluding weekends and break time

@HansVogelaar Hi i tried like this. Is this right? Why is it still error? What do u mean by making a list of holidays?

# Re: Calculate total working hours between date/time excluding weekends and break time

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Calculate total working hours between date/time excluding weekends and break time

There aren't many defined names so entering

``````= timeline
= start
= end
= holidays``````

should 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.

# Re: Calculate total working hours between date/time excluding weekends and break time

@HansVogelaar Here is the file idk why it doesn't work.

# Re: Calculate total working hours between date/time excluding weekends and break time

You hadn't defined a named range Holidays. I have removed Holidays from the code, and I have made it return 0 if the start and end dates are empty.

# Re: Calculate total working hours between date/time excluding weekends and break time

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)
)
)``````

# Re: Calculate total working hours between date/time excluding weekends and break time

thank you so much. I can use it and it works just fine.

# Re: Calculate total working hours between date/time excluding weekends and break time

@Peter Bartholomew 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.

1 best response

Accepted Solutions
best response confirmed by VladLockhart (Copper Contributor)
Solution

# Re: Calculate total working hours between date/time excluding weekends and break time

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.