Help

Re: Convert Seconds to h:mm in forumla

Solved
Jump to Solution
2136 0
cancel
Showing results for 
Search instead for 
Did you mean: 
LouD82
5 - Automation Enthusiast
5 - Automation Enthusiast

I’m having trouble with something that seems very simple. I’d like where it says “0.9 available” for it to show that time formatted as “h:mm”, as it does in the “Time Available” field next to it.

image

Here the formula for the Name field:

image

Essentially I think I need to convert seconds to hours, minutes and seconds within a formula, but I can’t find a way to do it and feel I’m probably just missing something simple. Thanks so much.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

I tried a bunch of ways to parse/convert the duration back into a datetime item, but it wouldn’t process cleanly, so I ended up force-building an h:mm format by doing the math, which was an interesting exercise by itself. :grinning_face_with_big_eyes: Here’s the full result, which will work for all time durations that I tested (replace TODAY() with your date field):

DATETIME_FORMAT(TODAY(), "M/DD/YYYY") & " - " & ROUNDDOWN({Time Available}/3600, 0)
& ":" & ROUND(({Time Available}/60) - (60*ROUNDDOWN({Time Available}/3600, 0)),0)
& " available"

And here’s a screenshot:

42%20PM

See Solution in Thread

10 Replies 10
M_k
11 - Venus
11 - Venus

Hi @LouD82

I found some documentation about creating formulas in Airtable.

If you scroll to the bottom of the page there is a link to all the formulas for dates and times.

Here is the link:

Hope it helps.

Mary

If I understand you correctly, you want that amount of time rounded to the nearest hour, so that 0:55 (55 minutes) becomes 1:00? Is that correct?

Never mind. I was assuming too much. :slightly_smiling_face: What you’re seeking is a way to convert 0.9 (in that example) into the time format. However, I’m still not quite sure why you’re rounding the time calculation, but that’s not important.

Off to experiment some more…

Back to questions again. Are you trying to simply echo the “0:55” time in your Name field, so that it looks like:

2/18/2019 - 0:55 available

Is that the goal?

Hi Justin, thanks so much for your help. Yep, that’s the goal exactly. I’m rounding the time calculation just for readibility, because otherwise it looks like this:

image

Again, I appreciate your help!

Justin_Barrett
18 - Pluto
18 - Pluto

I tried a bunch of ways to parse/convert the duration back into a datetime item, but it wouldn’t process cleanly, so I ended up force-building an h:mm format by doing the math, which was an interesting exercise by itself. :grinning_face_with_big_eyes: Here’s the full result, which will work for all time durations that I tested (replace TODAY() with your date field):

DATETIME_FORMAT(TODAY(), "M/DD/YYYY") & " - " & ROUNDDOWN({Time Available}/3600, 0)
& ":" & ROUND(({Time Available}/60) - (60*ROUNDDOWN({Time Available}/3600, 0)),0)
& " available"

And here’s a screenshot:

42%20PM

LouD82
5 - Automation Enthusiast
5 - Automation Enthusiast

WOW. Thanks, Justin. Plugged it in and it works flawlessly. It seems so silly that there isn’t a way just to convert or “flatten” the Time Available field to a string, then just plug it in to the formula. Oh well. Your efforts are greatly appreciated, that makes it much easier to schedule my tasks that trying to convert from duration to decimal in my head. :joy:

Happy to help! :thumbs_up: Yes, I agree that having a force-to-string function would be hugely helpful for situations like this. There’s VALUE() for turning strings into numbers, but why not the other way around?

Dana_Berkowitz
5 - Automation Enthusiast
5 - Automation Enthusiast

I was able to maintain the format of a duration field using
ROUNDDOWN(({Time Available}/3600), 0) & ":" & ROUND(MOD(({Time Available}/60), 60))

Also you can force-to-string in Airtable by adding & “” in the formula. The problem with this approach on a duration field is that it forces the decimal version to string, not the duration.

Ricardo
7 - App Architect
7 - App Architect

Both formulas produce results like 4:6 or 6:0 when I hope to see 4:06 or 6:00.

How did you avoid those?

ROUNDDOWN({Time Available}/3600, 0)
& ":" & ROUND(({Time Available}/60) - (60*ROUNDDOWN({Time Available}/3600, 0)),0)

ROUNDDOWN(({Time Available}/3600), 0) & ":" & ROUND(MOD(({Time Available}/60), 60))

Update, used instead:

DATETIME_FORMAT(DATEADD(TODAY(),{Time Available}&"",'s'),'H:mm')