Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
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.
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.)
Jun 27, 2019 11:13 AM
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.
Jun 27, 2019 11:17 AM
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.)
Jun 27, 2019 12:03 PM
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.
Jun 27, 2019 12:12 PM
What type of field is {Total Time Worked}
?
Jun 27, 2019 12:13 PM
{Total Time Worked} is a formula field with the equation: DATETIME_DIFF({Shift End},{Start Shift})
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.)
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
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.
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}
)
Jun 27, 2019 12:51 PM
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!