Help

Re: Roundup function in an IF statement

1149 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Greg_Stevens
4 - Data Explorer
4 - Data Explorer

I am attempting to write an equation but I cannot get Airtable to do what I want. What I am asking is that if “Total Time Worked” is greater than or equal to 7 hours and 45 minutes to round it up to 8 hours. However if “Total Time Worked” is less than 7 hours and 45 minutes do nothing and leave as is. I am trying to calculate this for payout purposes at work and making sure that anyone working at least 7:45 gets a full 8 hours but anyone working 7:44 hours or less simply gets paid for the time they worked.

This is what I wrote: IF({Total Time Worked} >= 7.75, ROUNDUP(7.75,0))

What am I doing wrong here? Any help would be greatly appreciated. Problem here is once this equation is entered what Airtable is now doing is round all hours above 7.5 to 8.

11 Replies 11

You don’t have an ‘else’ part to that IF() statement. The ‘else’ is optional — except in your case, you need two branches:

IF(
    {Total Time Worked}>=7.75,
    ROUNDUP(7.75,0),
    {Total Time Worked}
    )

I bet you have the formula field formatted as an integer. :winking_face:

(I didn’t catch it at first, either – I had to enter it into Airtable to figure out how it failed.)

Greg_Stevens
4 - Data Explorer
4 - Data Explorer

Thank you for your reply! What should I have it set as? I have it set as a decimal for the formatting. Still even with the equation you’ve provided me it is rounding anything above 7:30 to 8.

Double-check how the formula field is formatted. If it’s set to an integer, 7.5 will show as 8. If it’s set as a decimal with a precision of at least 1.0 it will show as 7.5. (A decimal with a precision of 1 is an integer.)

Greg_Stevens
4 - Data Explorer
4 - Data Explorer

I do have my formatting set as Decimal with the precision of 1.0 and still getting everything set to 8. I have one person who worked 7:41 and one person who worked 7:49 and with the equation plugged in it is giving both of these individuals 8 hours.

What type of field is {Total Time Worked}?

Greg_Stevens
4 - Data Explorer
4 - Data Explorer

{Total Time Worked} is a formula field with the equation: DATETIME_DIFF({Shift End},{Start Shift})

Ah… That is giving you the time in seconds. Try

DATETIME_DIFF({Shift End},{Start Shift})/3600

From the help center description of DATETIME_DIFF():

Returns the difference between datetimes in specified units. Default units are seconds. (See list of unit specifiers here.)

Greg_Stevens
4 - Data Explorer
4 - Data Explorer

Okay so I have it set up like this. I have total time worked with lunch as: DATETIME_DIFF({Shift End},{Start Shift}) then I have total time worked as: ({Total Time worked with lunch}-{Lunch Time}). THEN I have this last cell labeled Realized time as: IF({Total Time Worked}>=7.75,ROUNDUP(7.75,0),{Total Time Worked}).

Sorry I misspoke my last reply

Greg_Stevens
4 - Data Explorer
4 - Data Explorer

when I added the /3600 to the end of the equation it made my calculated time of 7:41 to 00:00.

In that case, I’m guessing {Total Time Worked} is formatted as a duration, not a number. It’s the same issue: durations also default to seconds. In either case, you’re comparing ~27,900 to 7.75: It’s always going to round up.

If you want to keep {Total Time Worked} as a duration — which makes sense, as it will display in h:mm format — but your calculated (rounded up) hours to be decimal, your formula needs to be

IF(
    {Total Time Worked}>=7.75*3600,
    ROUNDUP(7.75,0),
    {Total Time Worked}/3600
    )

(Or you could always just replace ROUNDUP(7.75,0) with 8.)

Alternatively, if you want both {Total Time Worked} and the calculated field to be durations, you need

IF(
    {Total Time Worked}>=7.75*3600,
    ROUNDUP(7.75,0)*3600,
    {Total Time Worked}
    )
Greg_Stevens
4 - Data Explorer
4 - Data Explorer

OMG it worked! Thank you so much. If you were here I could almost kiss you! I have been stuck trying to do this for almost 24 hours and you’ve helped me so much. A thousand time over thank you W_Vann_Hall!