Forum Discussion

MoreSassyThanSavvy's avatar
MoreSassyThanSavvy
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 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]))

excel
Formulas and Functions

5 Replies

  • NowshadAhmed's avatar
    NowshadAhmed
    Iron Contributor
    Dec 28, 2021

    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.

  • NowshadAhmed's avatar
    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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 28, 2021

    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.

Resources