Getting days in dd:hh:mm:ss to display as total hours above 99

[Jonathan1994]

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?

[#2]

@Jonathan1994 

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.

[#3]

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

[#4]

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

[#5]

@Jonathan1994 

Are your dd:mm:hh:ss values text values or real date/time values?

[#6]

@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 ???

[#7]

@Hans Vogelaar 

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.. 

[#8]

@Mounika_Kondaparthy 

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")

[#9]

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

[#10]

@Mounika_Kondaparthy 

I don't understand what you're doing there.

4398577

50.90946

50:21:49:37

4398577

=A2/86400

=INT(B2)&":"&TEXT(B2,"hh:mm:ss")