SOLVED

New Contributor

# formula for weekday and overtime

hello,

I need help for a formula. I have been struggling with this. Grateful to have some help.

The week starts on Monday. From Monday to Friday the working hours are 8hrs and on Saturdays it is 5 hours.

I need to calculate the overtime based on the total hours per day(th) in cell.  The conditions are as follows.

1. if the (th)cell is 0:00, then it must return 0:00,
2. if it is weekday(mon to fri) it must deduct 8 hours from the total hours
3. if the total hours for the weekdays is less than 8hr or 8hrs it must return 0:00 for the overtime
4. if it is a Saturday , 5hrs must be deducted from the (th)cell
5. if the (th) is less than 5hrs for saturday, it must return 0:00 as overtime
6. if it is a sunday, it must return same value hrs as in cell(th)

5 Replies

# Re: formula for weekday and overtime

Here is an example:

The formula in C2 is

=MAX(B2-CHOOSE(WEEKDAY(A2),0,8/24,8/24,8/24.8/24,8/24,8/24,5/24),0)

best response confirmed by ASP-01 (New Contributor)
Solution

# Re: formula for weekday and overtime

Thank you so so much! i was struggling with this for a long time.  Thank you so much for helping me out.

# Re: formula for weekday and overtime

Hello,

I have got a problem with the formula.
for wednesday it is sunbstrating 20 minutes instead of 8 hrs.

I have tried to remove the dot in the formula and replace it by a comma, then it substracts the 8 hours. However, then for saturday it shows 0:00 hrs even when there is overtime.