SOLVED

formula for weekday and overtime

New Contributor

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)

 

Please help.

5 Replies

@ASP-01 

Here is an example:

S0651.png

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

@Hans Vogelaar 

 

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

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.

Can you please help

@ASP-01 

Oh dear, what a stupid typo. Sorry! It should have been

 

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

Thank you, really worked well.