Help

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.

Convert minutes past the hour to a fraction

Topic Labels: Formulas
Solved
Jump to Solution
869 4
cancel
Showing results for 
Search instead for 
Did you mean: 

I’ve got a datetime field called Start time (UTC)

I want to take the time component and reformat the minutes into a fraction.
For example:

Source Desired output
14:00 14
14:15 14.25
14:30 14.5
14:55 14.92

I have tried taking the minute component and conditionally dividing it by 60 to at least get started (before I sort out rounding etc), but I just get #ERROR. Here’s my formula:

IF(DATETIME_FORMAT({Start time (UTC)},'m')=0,"",(DATETIME_FORMAT({Start time (UTC)},'m')/60))
1 Solution

Accepted Solutions

Hi there!

I think you’re running into issues because DATETIME_FORMAT() returns a string. You can convert the value to a number by wrapping it in the VALUE() formula.

Try this:

IF({Start Time (UTC)}, VALUE(DATETIME_FORMAT({Start Time (UTC)}, 'm'))/60)

You’ll also want to make sure your formula field is formatted as a number with decimals (after you set the formula), or else you’ll only return integers. Hope this helps! :slightly_smiling_face:

EDIT: Just realized this only solves half of the problem. Will update with more!

See Solution in Thread

4 Replies 4

Hi there!

I think you’re running into issues because DATETIME_FORMAT() returns a string. You can convert the value to a number by wrapping it in the VALUE() formula.

Try this:

IF({Start Time (UTC)}, VALUE(DATETIME_FORMAT({Start Time (UTC)}, 'm'))/60)

You’ll also want to make sure your formula field is formatted as a number with decimals (after you set the formula), or else you’ll only return integers. Hope this helps! :slightly_smiling_face:

EDIT: Just realized this only solves half of the problem. Will update with more!

The following should yield the hours as well a decimal value for the minutes :slightly_smiling_face:

IF({Start Time (UTC)}, SUM(VALUE(DATETIME_FORMAT({Start Time (UTC)}, 'h')), VALUE(DATETIME_FORMAT({Start Time (UTC)},'m'))/60))

This is great, thank you - VALUE was what I needed.

This is the formula I ended up with :

DATETIME_FORMAT({Start time (UTC)},'H') & IF(VALUE(DATETIME_FORMAT({Start time (UTC)},'m'))/60=0,'', SUBSTITUTE(VALUE(DATETIME_FORMAT({Start time (UTC)},'m'))/60&"",'0',''))

You can also directly get the numbers of hours and minutes (without having to use DATETIME_FORMAT or VALUE, by using the HOUR() and
MINUTE() functions. They are documented in the formula field reference.

HOUR({Start time (UTC)} + (MINUTE({Start time (UTC)}) / 60)

This will result in a number (instead of a string) and you can use field formatting to adjust the number of decimal places.