How to convert Time/Text format to Hours format
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!
Answers
-
Are you able to provide some examples of the expected output?
-
Sorry, this is what I am trying to achieve:\
-
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
?
-
Yes you are correct. Looking for the following format:
15 Mins = 0.15
-
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?
-
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"?
-
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"?
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!