Format time spent from 00:00:00 to 0.00

[Daniel_Kuttner]

Hi Guys,

we are currently trying to import timesheets from toggl to airtable. toggl naturally saves time in the format: 00:20:24, but this is in airtable currently not calculatable. The conversion of the fields doesn’t work correctly like you can see on the screenshots:


Is there any formula or possibility to convert this format in airtable correctly? Meaning from 00:00:00 to 0.00. With the help of excel we can calculate the times be mulit-selecting fields and manually write the sum down, but we just try to stay in airtable :winking_face:

Many thx for your help and best regards
Daniel

[W_Vann_Hall]

I’m assuming Airtable interprets Toggl’s 00:00:00-formatted text field as being the equivalent of an Airtable duration — and the fundamental unit of an Airtable duration is the second. Accordingly, the conversions you show are correct; for instance,

00:20:24

equals

20 (minutes) + 24 (seconds)

equals

1200 (seconds) + 24 (seconds)

equals

1224.00

If, when you say you want to convert from

Daniel_Kuttner:

00:00:00 to 0.00

you mean convert from hours:minutes:seconds to minutes:fractional minutes use the formula

VALUE(Duration)/60

For hours:fractional hours use

VALUE(Duration)/3600

[Daniel_Kuttner]

Great, many thx for your big help @W_Vann_Hall ! Your hint worked like a charm. Just one more question:
How could the converted number be rounded to the next nearest 0.25 hour? meaning if it’s 0.3 it should be 0.25, if it’s 0.4 it should be 0.5?

Many thx in advance again and best regards
Daniel

[W_Vann_Hall]

I think this will do it. This assumes {Time} is a number representing — well, I guess it doesn’t really care what {Time} represents, just as long as it’s a number.

INT({Time})+(ROUND(MOD({Time},1)*4))/4

Remember to set formatting for the field containing this formula to a decimal with two significant digits.

Edit: I realize this doesn’t apply to you, @Daniel_Kuttner, but for anyone else looking to repurpose this routine, in its current form the algorithm is only valid for positive values.

[Daniel_Kuttner]

Many thx again @W_Vann_Hall , this worked perfectly for me and now everything is rounded correctly. One last question again:
is there a way to round it to the next bigger 0.25 unit? like when it`s 0.1 round to 0.25?
Happy eastern by the way and best regards

[W_Vann_Hall]

Daniel_Kuttner:

is there a way to round it to the next bigger 0.25 unit? like when it`s 0.1 round to 0.25?

Yep:

INT(time)+(ROUNDUP(MOD(time,1)*4,0))/4