I have a calculation field that sums time from a portal. Because the portal defines time units in decimals, so does my calc field but I need it to convert the decimals into proper format: hh:mm:ss (ex. 1.75 hours needs to show up as 01:45:00)

My current code:

Sum ( Time Sheet::Hours ) 

 

Time is in seconds. Assuming your sum is a time field, multiply it by 3600.

Thanks, that does it.

...would you know how to pad the hours with a leading zero? Currently it displays as 1:45:00, I'd rather have it as 01:45:00

1 minute ago, McScripta said:

how to pad the hours with a leading zero?

You have that option when you format the field using the Inspector.

I actually need this field as one to export to tab-separated text therefore I have to have the padding in raw value of the field itself...

.tab files don't themselves require any 'padding' - the tab provides the field separation.

The end consumer of the file may require two digit hours - you'll need to have another calc field to perform the padding before export in that case.

2 minutes ago, webko said:

The end consumer of the file may require two digit hours - you'll need to have another calc field to perform the padding before export in that case.

yes, that is my case. I was hoping that the padding could be rolled into the same calculation I'm already performing

Sum ( Time Sheet::Hours ) * 3600

 

11 minutes ago, McScripta said:

I actually need this field as one to export to tab-separated text therefore I have to have the padding in raw value of the field itself...

No, you don't have to do that. You can format the field on the layout to use a leading zero for hours, and check the "Apply current layout's data formatting..." option when exporting.

That's the easiest way - but if you don't want to put the field on the layout, you can make your calculation =

Let (
t = Sum ( Time Sheet::Hours ) * 3600
;
SerialIncrement ( "00" ; Hour ( t ) ) & SerialIncrement ( ":00" ; Minute ( t ) ) & SerialIncrement ( ":00" ; Seconds ( t ) )
)

 

 

Edited June 16, 20169 yr by comment

I'd do it comment's way too. But just FYI here's how you do that type of padding:

Right ( 0 & yourTimeCalc ; 8 )

12 minutes ago, comment said:

No, you don't have to do that. You can format the field on the layout to use a leading zero for hours, and check the "Apply current layout's data formatting..." option when exporting.

...that doesn't seem to do it. My field is type Calculation with calculation result set to Time

12 minutes ago, McScripta said:

...that doesn't seem to do it.

Please describe exactly what happens when you try it.

 

 

Edited June 16, 20169 yr by comment

22 minutes ago, comment said:

You can format the field on the layout to use a leading zero for hours, and check the "Apply current layout's data formatting..." option when exporting.

And once again, I learn something, which is a great thing for any day... Thanks

10 minutes ago, comment said:

Please describe exactly what happens when you try it.

Please disregard, crossed posts. That was in regards to your earlier suggestion about applying current formatting on export.

This worked perfectly:

Let (
t = Sum ( Time Sheet::Hours ) * 3600
;
SerialIncrement ( "00" ; Hour ( t ) ) & SerialIncrement ( ":00" ; Minute ( t ) ) & SerialIncrement ( ":00" ; Seconds ( t ) )
)

Thanks all for pitching in and your help!