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
869 4
cancel
Showing results for
Did you mean:
4 - Data Explorer

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
10 - Mercury

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!

4 Replies 4
10 - Mercury

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!

10 - Mercury

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

4 - Data Explorer

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',''))``
18 - Pluto

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.