Feb 27, 2020 02:14 PM
Hi everyone! I have done several searches and cannot find exactly what I am trying to do. I am relatively new to all of this and my ADHD brain struggles with all of the technical stuff at first, so I apologize if this is very obvious.
I want to simply have a “total hours” field that takes the start and end date/time fields and puts it in hours rounded to the nearest quarter. 2/27/2020 8:00AM to 2/27/2020 9:45 would automatically compute to 1.75.
This is for a timesheet :slightly_smiling_face:
Thanks!
Solved! Go to Solution.
Feb 27, 2020 08:54 PM
Existing fields:
The goal is a field showing the duration between the start and end times
You want a formula field that uses the DATETIME_DIFF
function. It and all the other functions in this post are documented in the formula field reference. In addition to the fields with the start and end times, the function takes a unit specifier. The unit specifier for hours is h
.
DATETIME_DIFF({End}, {Start}, 'h')
If you do not need rounding, you can just use this formula. In order to see the decimal part, set the number of decimals in the formatting tab when defining the field.
If you do need rounding to the nearest quarter hour, read on …
The rounding functions in Airtable round to a specific decimal place, not to a quarter of an hour. Thus we need figure out the logic behind what functions and operators to combine.
There are many ways to go about this task, including this formula with 8 lines and this formula with 5 lines. I also created several possibilities (including one with 15 lines) before settling on this one:
Use the same function, but change the unit specifier.
DATETIME_DIFF({End}, {Start}, 'm')
An internet search for how to round a number {n} to the nearest multiple of another number {m} produced this formula:
ROUND({n}/{m})*{m}
Thus, to round the number of minutes to the nearest multiple of 15:
Divide the minutes remaining by 15
DATETIME_DIFF({End}, {Start}, 'm') / 15
Round the result to the nearest whole number
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15)
Multiply times 15
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) * 15
Divide by 60 to convert minutes to hours
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) * 15 / 60
Notice that the last two operations are multiplying by 15 and dividing by 60. When combined those two operations are the same as simply dividing by 4.
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) / 4
Make sure you set the number of decimal places in the formatting tab when defining the field, or you won’t see the decimal part.
The above formula will produce an #ERROR!
if there is no start or end time.
To avoid the unsightly error, wrap the formula in an IF
function that tests for the existence of values in both of the date/time fields.
Here is the final formula.
IF( AND({End}, {Start}),
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) / 4
)
Feb 27, 2020 08:54 PM
Existing fields:
The goal is a field showing the duration between the start and end times
You want a formula field that uses the DATETIME_DIFF
function. It and all the other functions in this post are documented in the formula field reference. In addition to the fields with the start and end times, the function takes a unit specifier. The unit specifier for hours is h
.
DATETIME_DIFF({End}, {Start}, 'h')
If you do not need rounding, you can just use this formula. In order to see the decimal part, set the number of decimals in the formatting tab when defining the field.
If you do need rounding to the nearest quarter hour, read on …
The rounding functions in Airtable round to a specific decimal place, not to a quarter of an hour. Thus we need figure out the logic behind what functions and operators to combine.
There are many ways to go about this task, including this formula with 8 lines and this formula with 5 lines. I also created several possibilities (including one with 15 lines) before settling on this one:
Use the same function, but change the unit specifier.
DATETIME_DIFF({End}, {Start}, 'm')
An internet search for how to round a number {n} to the nearest multiple of another number {m} produced this formula:
ROUND({n}/{m})*{m}
Thus, to round the number of minutes to the nearest multiple of 15:
Divide the minutes remaining by 15
DATETIME_DIFF({End}, {Start}, 'm') / 15
Round the result to the nearest whole number
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15)
Multiply times 15
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) * 15
Divide by 60 to convert minutes to hours
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) * 15 / 60
Notice that the last two operations are multiplying by 15 and dividing by 60. When combined those two operations are the same as simply dividing by 4.
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) / 4
Make sure you set the number of decimal places in the formatting tab when defining the field, or you won’t see the decimal part.
The above formula will produce an #ERROR!
if there is no start or end time.
To avoid the unsightly error, wrap the formula in an IF
function that tests for the existence of values in both of the date/time fields.
Here is the final formula.
IF( AND({End}, {Start}),
ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) / 4
)