Monday Formula to calculate overtime

Nonprofits
,
1

Hi everyone!
I have an interesting question today :slight_smile:

We want to use a Monday.com to track team members worked hours and overtime.
We have the following columns - start time (time), end time (time), planned daily hours (number), actual daily hours (formula), and overtime/absence (formula).

The “Actual daily hours” column has a formula, using HOURS_DIFF to substract start time from end time.

I need a formula to put into the “overtime/absence” column, that would substract Planned daily hours, from Actual Daily Hours, that would tell us how many hours overtime the person has made, right? Oh, and we would like to do this with two decimal places, so we know if the person made, lets say, 15-20-30 minutes overtime as well.

Is this the right way to go about calculating this, what would you recommend?

Thank you and have a great weekend!

Sara

2

Hi Sara,

If both times are on the same day, try:

LEFT({End Time#Minutes},2)-LEFT({Start Time#Minutes},2)+DIVIDE(MINUS(RIGHT({End Time#Minutes},2),RIGHT({Start Time#Minutes},2)),60)

Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*

1 Like
3

Dear Gilles,
Thank you very much for providing this formula, it works!
All the best,
Sara

4

Sara,

Is your overtime calculated on a daily or weekly basis?
is it time over 8hrs/day or time over 40hrs/wk?
different companies calculate based on different rules.

if it is daily. then you just need to have a column that calculates {time out} - {time in} , - 8. (anything over 8hrs is OT)
if it is weekly, each day you need a column to calculate hours worked by subtracting time in from time out.
then you need a separate table to add the sum of each days hours for the week. any hours over 40 are OT.

the specifics on this are a little more complicated. I just wanted to try to breakdown the logic behind it.
a Monday Formula Specialist should be more helpful in the specific formulas needed.

I hope you find an answer that helps. If you figure this out, please share with me. i am wanting to do something similar.
I am also trying to figure out a process for tracking PTO. :slightly_smiling_face: I’ll be glad to share my results once I figure this one out too.

Outlook-icxwbuay.png

Outlook-laq2u1zw.jpg
Outlook-laq2u1zw.jpg1200Ă—1200 61.4 KB

Outlook-h2isekte.png

Outlook-iudamjxu.png

Outlook-21secife.png

5

Dear Quentin,
See if the answer from Gilles here can help you. It worked for me! :slight_smile: Monday Formula to calculate overtime - #2 by GCavin

6

Hey Gilles, what columns do I need to setup prior to using this formula?

Outlook-icxwbuay.png

Outlook-laq2u1zw.jpg
Outlook-laq2u1zw.jpg1200Ă—1200 61.4 KB

Outlook-h2isekte.png

Outlook-iudamjxu.png

Outlook-21secife.png

7

Hi Quentin,

See the first post of the thread. I just responded to it. :slightly_smiling_face:

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.