Forum Discussion
Getting days in dd:hh:mm:ss to display as total hours above 99
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")
- Thank you for that formula..
But my value in Column A is in text form like this - 4398577
so i have made changes as =A2/86400 and custom format as "dd:hh:mm:ss".. when i gave your formula i got ans like this
Time
4398577 19:21:49:37 133:00:00:00
- 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 ???
Are your dd:mm:hh:ss values text values or real date/time values?
