Forum Discussion

VladLockhart's avatar
VladLockhart
Copper Contributor
Jul 15, 2024

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 18, 2024

VladLockhart 

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

 

  • VladLockhart's avatar
    VladLockhart
    Copper Contributor
    Jul 19, 2024

    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.

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 19, 2024

      VladLockhart 

      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.

Resources