Calculate workhours based on weekdays on range

[wahidfajar]

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!

[#2]

=(C4-B4)*24

[#3]

@wahidfajar 

Could you please explain what the regulations are, mainly how actual workhours are based on regular workhours.

[#4 · Accepted solution]

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

[#5]

Appreciate it for your answer. But recently I need a detailed calculation of actual working hours with the ranges in the existing table

[#6]

There is no difficult regulation, I just need to calculate working hours according to the weekday working hours on a range sir.

[#7]

Thank you, it works sir. It's dynamic too, that's cool. I really appreciate it!

[#8]

You're welcome!