formula for weekday and overtime

New Contributor



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


Here is an example:


The formula in C2 is


best response confirmed by ASP-01 (New Contributor)

@Hans Vogelaar 


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


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


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



Thank you, really worked well.