# Roundup function in an IF statement

Topic Labels: Formulas
1982 11
cancel
Showing results for
Did you mean:  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  13 - Mars

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.)  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.  13 - Mars

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.)  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.  13 - Mars

What type of field is `{Total Time Worked}`?  4 - Data Explorer

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

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.)  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  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.  13 - Mars

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}
)
``````  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! 