Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Convert minutes past the hour to a fraction

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
2422
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2020 07:42 AM

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

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2020 08:30 AM

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

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2020 08:30 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2020 08:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2020 09:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 16, 2020 10:42 AM

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.

Reply