How to calculate overtime in Microsoft Excel

**Purujeet** · 15-02-2012

I am trying to complete a spreadsheet in hour and minute format that will calculate the overtime for each employee over 40 hours. If I have someone with 45:30 hours and want to have excel calculate the hours of overtime in the next column What formula would I use.

=MAX(0,D35-40) doesn't work
=IF($D35>40,SUM($D35,-40)," ") doesn't work

**GALIENA** · 15-02-2012

Try the following syntax =MAX(0,D35-"40:00"). Format the cell as hh:mm. This formula offers the date format in cell in the form of hh:mm. I am not sure there is any kind of better alternative to do this, but you can try out something else also.

**DanielaA** · 15-02-2012

Try:=MAX(0,(D35*24)-40) or =IF(D36>$D$1,D36-$D$1," "). The process is a bit complicated, but if you have all the values in the sheet, then this can be easy to do. I will recommend you to test the above two formula one by one and provide conclusion on which one is working so that the same can help other also.