Formula for OT totals

New Contributor

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 totals for the week in cell M3. I have been sleuthing through internet tutorials, how-to videos, and a whole lot of these forum threads to no avail. I have a feeling I am adding(or missing) a bracket somewhere and I am quite new to this program.

 

Can anybody suggest what formula to use? So far I have tried the following:

=SUMIF(Table2[@[MONDAY]:[SUNDAY]]>8)

=(((SUMIF(E3>A100,[TOT],[0])+(F3>A100,[TOT],[0])+(G3>A100,[TOT],[0])+(H3>A100,[TOT],[0])+(I3>A100,[TOT],[0])+(J3>A100,[TOT],[0])+(K3>A100,[TOT],[0]))

Screenshot 2021-12-27 210810.png

5 Replies

@MoreSassyThanSavvy Not sure what A100 refers to in your formula and I don't believe you need SUMIF. Perhaps the attached workbook will point you in the right direction. The formula in column M, sums the numbers of hours greater than 8 for each day. If the hours on a particular day are below 8, then zero is returned for that day.

@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.

@MoreSassyThanSavvy or you could use the table given by @Riny_van_Eekelen 

It is more done and prepared for you.

 

If the result is still not to your liking, then it is probably the the format you are having problem with.

Use the time format on the Mr. @Riny_van_Eekelen's table I suggested earlier and input the values again to get timed results.

@Riny_van_Eekelen Thank you so much! This template has been exceptionally helpful.

Thank you! This formula worked spectacularly!