Convert Numbers to Time (Day, Hours, Minutes & Seconds)

[GemBox]

Hello Team,

How to convert the numerical value to time format.
I'm extracting the data from servicenow application to excel sheet, where the values are shown in numbers instead of time format.

I have numeric value in A2 (792134) which needs to be shown as 9 Days 4 Hours 2 Minutes

Thanks & Regards,
Gem

[Glenn Kennedy]

One way:

=INT(A2/86400)&" days "&INT(MOD(A2,86400)/3600)&" hours "&INT(MOD(MOD(A2,86400),3600)/60)&" minutes"

[Pepe Le Mokko]

This one is also funny

Please Login or Register  to view this content.

Alternatively you could use =A1/(24*60*60) and use the second part as custom format

[GemBox]

Thank you so much Glenn & Pepe..

[nick.williams]

Excel stores dates and times as numeric values, where 1 is a day (so 0.5 is 12 hours, etc). Your data is in seconds, so you need to divide that by 86400 (or 24*60*60) to convert it to days. Then it depends whether you want to have the actual value in the cell as your string, in which case Glenn's solution looks nice to me.

If you just want it to be displayed as you have said, but retain the actual date value so you can use it with other formulae easily, you can instead just use A1/86400 and use a custom format of:

d "Days" h "Hours" m "Minutes"

This however has the drawback that if you have over 31 days then it will go back to 1, so if that's possible in your data then it's not a great solution. I can't figure out how to resolve this. Looking around on Google it might not be possible with formatting only. Someone else might be able to say for sure.

NB You can do it with hours and minutes only, e.g.

[h] "Hours" m "Minutes"

will give "220 Hours 2 Minutes" in your example, but you can't do the same for days.