This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

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

Showing results for

- 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
1830
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