Converting Start and End Day/Time Fields into Number Rounded

[Nate_Sheets]

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!

[kuovonne · Accepted solution]

A duration rounded to the nearest quarter hour

Existing fields:

• {Start} a date/time field for when the task starts
• {End} a date/time field for when the task ends

The goal is a field showing the duration between the start and end times

• in hours
• in decimal format
• rounded to the nearest quarter of an hour

Getting the duration between two 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 logic for rounding a duration to a quarter hour

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:

1. Calculate the number of minutes
2. Round the number of minutes to the nearest multiple of 15
3. Convert minutes to hours

Calculating the number of minutes

Use the same function, but change the unit specifier.
DATETIME_DIFF({End}, {Start}, 'm')

Rounding the number of minutes

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:

1. Divide the minutes remaining by 15
   DATETIME_DIFF({End}, {Start}, 'm') / 15

2. Round the result to the nearest whole number
   ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15)

3. Multiply times 15
   ROUND(DATETIME_DIFF({End}, {Start}, 'm') / 15) * 15

Convert minutes to hours

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.

Tidying things up

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
)