This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Roundup function in an IF statement

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
2632
11

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 11:02 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 11:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 11:13 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 11:17 AM

`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.)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:03 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:12 PM

What type of field is `{Total Time Worked}`

?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:20 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:25 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:30 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:48 PM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 27, 2019 12:51 PM