Forum Discussion
Getting days in dd:hh:mm:ss to display as total hours above 99
Hey folks,
Working on a big data set of times elapsed, and I was wondering if there was a way to have more than 2 digits displaying the amount of days spent on something. I know the total time frame is approximately 525,600 hours worth of time, but the data displays in dd:hh:mm:ss so I've been sticking to said format. Would there be a way to convert this into say hhhhhh:mm:ss?
- Yep that works. Go to Format cells /Custom and towards the bottom you will find the hh:mm etc section ... and manually insert [h].. Works a treat
dd is strictly limited to days of the month, so it won't go above 31.
You can use the custom format [hh]:mm:ss
The [ ] around hh tell Excel to treat the hours as cumulative time instead of as clock time.
Can you please suggest, how can i get the data for dd:hh:mm:ss where the time is 38days 20 hours 49 minutes and 02 seconds..
When i gave the custom field as dd:hh:mm:ss for the above data.. i am getting the data as 8:20:49:02. Please help me..As I mentioned in an earlier reply, d or dd in a custom date format is the day of the month, so it can only have the values 1, 2, 3, ..., 31. It is not possible to create a custom date format that displays 38 as the number of days.
As an alternative, you can use a formula in another cell to display the value the way you want:
The formula in B2 is
=INT(A2)&":"&TEXT(A2,"hh:mm:ss")
- Interesting, is there a function that'd allow me to convert that dd:hh:mm:ss into [hh]:mm:ss? In an attempt to simply input the data as is into a sum function and formatting the cells to [hh]:mm:ss only netted me a display of 00:00:00
Jonathan1994 Hi Johnathon. Please see Attached spreadsheet. I have just created random hrs mm and ss and they have calculated fine. If this still doesn't help can you attach a sample of your sheet with anonymous data so I can have look ???
