Add minutes while summing hours

Platform discussions
,
1

I have created a table that includes the entry times and the departing times of bartenders. In that table I also want to have the amount of hours worked per day.

The issue is sometimes the shift of a bartender starts at 8pm and finishes at 2:30am, which is a different day, and the formula HOURSDIFFER won’t work.

I have created this formula IF({Start Time}>{End Time};{End Time}+24-{Start time};{End time}-{Start time})

It returns a correct result but it doesn’t take into account minutes.

For example, a bartender that has worked from 8pm to 2:30 am, the formula will return 7 hours instead of 6.5

How can I do that?

2

Hi Pau,

Try

HOURS_DIFF({End Time},{Start Time})

Want to break free of the formula column and write calculation results to any type of column? Take a look at the Advanced Formula Booster.

3

Hello, Gilles!

Thanks for your reply!

The thing with HOURS_DIFF({End Time},{Start Time}) is that it returns an incorrect value when the End Time is on another day.

For example, the bartender starts working on Friday at 8pm and ends on Saturday at 2:30 am. She has worked 6 hours and a half, but the HOURS_DIFF formula returns a negative value.

4

I think the solution without using a 3rd party app is to use the time tracking column.

With the Advanced Formula Booster, it can be done easily. For instance:

2023-11-23_09-25-23

5

Hi @pauroca,

The DAYS() function takes minutes into time. So, you can use a formula like this:

MULTIPLY(
    DAYS(
        {End Time},
        {Start Time}
    ),
    24
)
6

To do this, I should change the Entry Time column into a Date Column, right?

7

Correct. What type are they now? Hours columns? I would definitely recommend using Date columns for this.