Forum Discussion

Copper Contributor

Dec 28, 2021

Formula for OT totals

Hello everybody, I have been charged with creating and maintaining a spreadsheet to track our employees OT, however, I am struggling to correctly create the necessary formula to calculate the OT tot...

excel

Formulas and Functions

NowshadAhmed

Iron Contributor

Dec 28, 2021

MoreSassyThanSavvy First, I would suggest you to use 'Time' format on the cells to show the time properly. This will make it easier for you to follow and present. If you use the format on current values, you'll get wrong results. I assume you should have a table where the employee times are recorded? If you are entering the values manually, use the time format. Like for 1 hour and 30 minutes over time, set the cell format to 'Time' and enter 1:30.

For the Hours Total use: something like:

=SUM(Table2[[MONDAY]:[SUNDAY]]

Use '[h]:mm' custom cell format for this.

For the OT: Again, set the format to 'Time' or better use custom format '[h]:mm' and use a formula like:

=SUMIF(Table2[[MON]:[SUN]],">8:00")-COUNTIF(Table2[[MON]:[SUN]],">8:00")*TIME(8,0,0)

You need to adjust to your needs.

It might get tricky for -ve values, if you want to highlight them.