Forum Discussion
Jonathan1994
May 06, 2022Copper Contributor
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 fra...
Mounika_Kondaparthy
Mar 14, 2023Copper Contributor
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..
HansVogelaar
Mar 14, 2023MVP
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")
- Mounika_KondaparthyMar 16, 2023Copper ContributorThank 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- HansVogelaarMar 16, 2023MVP
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")