Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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.
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!
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!
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))
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',''))
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.