I pull a lot of data that comes in as h:mm:ss, when it's under an hour it shows as :59:59 and I need it to show as 0:59:59. Any way to set something up that can automatically do that so I do not need to go into each cell and add a 0?
The format is already in h:mm:ss, if it is under one hour nothing is added for h. I have tried Format cells > Custom > h:mm:ss, and Format cells > Custom > [h]:mm:ss and it still shows :19:53 for instance and I need it to be 0:19:53
Below are a few examples as I just pulled the data:
:19:05 = :19:05
1:04:44 = 1:04:44 AM
I have tried multiple formats for time and it continues to stay as :19:05
I pull the data in a report from a program called CMS, when pasted into excel anything under 1 hour shows in it's natural text form (with the hour slot missing). Anything over an hour comes in as h:mm:ss, 8:30:09 = 8:30:09 AM. When I manually add a 0 to a cell that needs one, it changes from :18:26 to 12:18:26 AM and diplays in the cell as 0:18:26
It is a display format that is causing the problem, I need to force display a 0 before the : when it is blank. Once the 0 is added everything is perfect and my conditional formats work.