Forum Discussion

wahidfajar's avatar
wahidfajar
Copper Contributor
Oct 12, 2023
Solved

Calculate workhours based on weekdays on range

Hi!

I have some problems calculating actual work hours for my employees based on our company regulations. Here is sample data that we need to calculate (attachment).

I have been trying to calculate with some formulas but still haven't solved the problem.

 

Please give me some advice or formulas to get me the answer.

Thank you!

admin
developer
excel
Excel for web
Formulas and Functions
office 365
training
  • Patrick2788's avatar
    Patrick2788
    Oct 12, 2023

    wahidfajar 

    This is similar to a recent request for 'down time' hours.  The idea is to create an array from start to finish for a given row with SEQUENCE incrementing by 1 minute.  Then it becomes a matter of filtering.

     

    ActualWorkHours(start,finish)
=LET(
    one_minute, 0.000694444444444444,
    minutes_in_a_day, 1440,
    WorkHours, LAMBDA(start, finish,
        LET(
            minutes, (finish - start) * minutes_in_a_day,
            time_arr, SEQUENCE(minutes, , start, one_minute),
            hr_mm, time_arr - INT(time_arr),
            day_of_week, WEEKDAY(time_arr, 2),
            total, COUNT(
                FILTER(
                    time_arr,
                    (day_of_week = 6) * (hr_mm >= 8 / 24) * (hr_mm <= 14 / 24) +
                        (day_of_week <= 5) * (hr_mm >= 8 / 24) * (hr_mm <= 16 / 24),
                    ""
                )
            ) / 60,
            IF(COUNT(start, finish) = 2, total, "")
        )
    ),
    MAP(start, finish, WorkHours)
)

       

7 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 12, 2023

    wahidfajar 

    This is similar to a recent request for 'down time' hours.  The idea is to create an array from start to finish for a given row with SEQUENCE incrementing by 1 minute.  Then it becomes a matter of filtering.

     

    ActualWorkHours(start,finish)
=LET(
    one_minute, 0.000694444444444444,
    minutes_in_a_day, 1440,
    WorkHours, LAMBDA(start, finish,
        LET(
            minutes, (finish - start) * minutes_in_a_day,
            time_arr, SEQUENCE(minutes, , start, one_minute),
            hr_mm, time_arr - INT(time_arr),
            day_of_week, WEEKDAY(time_arr, 2),
            total, COUNT(
                FILTER(
                    time_arr,
                    (day_of_week = 6) * (hr_mm >= 8 / 24) * (hr_mm <= 14 / 24) +
                        (day_of_week <= 5) * (hr_mm >= 8 / 24) * (hr_mm <= 16 / 24),
                    ""
                )
            ) / 60,
            IF(COUNT(start, finish) = 2, total, "")
        )
    ),
    MAP(start, finish, WorkHours)
)

       

    • wahidfajar's avatar
      wahidfajar
      Copper Contributor
      Oct 13, 2023
      There is no difficult regulation, I just need to calculate working hours according to the weekday working hours on a range sir.
    • wahidfajar's avatar
      wahidfajar
      Copper Contributor
      Oct 13, 2023
      Appreciate it for your answer. But recently I need a detailed calculation of actual working hours with the ranges in the existing table

Resources