How to convert Time/Text format to Hours format

jcharger13

Hello Everyone! I am trying to find out how to convert a time/text format to an hours format. I have tried reaching out to the data source and they can't modify the formatting. Also this is a monthly data pull so I could manually edit prior to uploading to Smartsheets however was trying to prevent from having to do that. Any help would be greatly appreciated!

Paul Newcome

Are you able to provide some examples of the expected output?

jcharger13

Sorry, this is what I am trying to achieve:\

Paul Newcome

So I want to confirm... You want the minutes as a decimal and not necessarily as the part of the hour.

You want

15 Mins = 0.15

as opposed to 15 minutes be a quarter of an hour

15 Mins = 0.25

?

jcharger13

Yes you are correct. Looking for the following format:

15 Mins = 0.15

Paul Newcome

Ok. So what happens if we have something that is more than 100 minutes such as 2 hours? In your screenshot you have 1 hour and 15 minutes as 0.75. Would 2 hours be 1.20?

jcharger13

I could do Quarter of an hour as indicated above by you. I have been having to do some manual editing of the data sources and the time aspect has been the struggle to get them to align.

Currently they show as:

I just need to find a format that I can adjust the originals to so I can then use them in my reporting.

For the one in seconds (right side screenshot) i was using: Time in seconds/3600. So in that example it returns 1.49

I guess I could convert the 45 min (nothing I can do about the input formatting) from 45 min to seconds (45*60) and then divide by 3600 to get .75, then both would be in the same format in excel. The question is will Smartsheets be able to add up all of the allotted times to an Hour format so I can say "Johnny has an accumulated training time of 22.5 hours"?

Paul Newcome

My suggestion would be to convert it all into hours as a decimal so 1 hour and 45 minutes ends up as 1.75.

Is there the possibility that there would only be hours such as "1 Hrs"?

jcharger13

I ended up doing the conversions in excel, copied it to another spreadsheet and pasted as values then uploaded to Smartsheets.

Sadly due to the Data Sources formatting I can't breakaway from some of the manual editing required. So now I will have a Template, The Data File and then create an upload file once cleaned and copied to get the formatting I need.

I appreciate the help and ideas!