Forum Discussion

VladLockhart's avatar
VladLockhart
Copper Contributor
Jul 15, 2024
Solved

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...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jul 15, 2024

    VladLockhart 

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 16, 2024

VladLockhart 

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

 

 

VladLockhart's avatar
VladLockhart
Copper Contributor
Jul 17, 2024
Hi thanks for replying, i tried to change the input but it shows error #NAME?.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 17, 2024

    VladLockhart 

    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.

Resources