Custom Cell Format | Elapsed Time in Days (With Hours and Minutes)

[Saradomin]

Hello everyone.


Is there a way to use custom cell formatting to display time elapsed in days (with hours and minutes)?


I have two time stamps:

8/03/2022 15:56:00

and

20/05/2022 20:19:00


If I subtract the former from the latter, I get:

73.18263889

which is the number of days as a fraction.


Using the following custom formatting:

[hh] "hours and" mm "minutes"

displays the result in time elapsed in hours (with minutes), which is:

1756 hours and 23 minutes.


What I'm trying to do is display the results in time elapsed in days (with hours and minutes).

So, naturally, I tried:

[dd] "days," hh "hours and" mm "minutes"

but sadly, it didn't work.


Is there a solution to this using custom cell formatting?

Thanks in advance!

[MrShorty]

No, there is no "elapsed days" date/time number format that will do what you want. Excel can only do calendar days, so the longest that it can display as elapsed days is 31 days.

If you are willing to store the result as a text string, it should be possible to parse out days, hours, etc. and then join them together in a text string. Of course, numbers stored as text create plenty of other problems, so I would see this as only useful when the result is a "final" result and not an intermediate calculation.

[ELeGault]

Assuming your 2 dates were living in A1 and B1.

I ABS the INT because if you are doing a countdown you may not want to see negative days. But if you do want to see "-3 days..." then take off the abs.

As pointed out by MrShorty, you cannot do operational aggregates off the field after you force it to appear as a string... but it will display the text as desired.

You can see that each time part is wrapped in an IF including the Days. All optional... but it will not display seconds or minutes or hours or days if there are 0 of that unit to display.

Please Login or Register  to view this content.

It can be much smaller if you are good with results that always display the parts...

3 days 0 hours 2 minutes 12 seconds

Please Login or Register  to view this content.

If you need to do any operations against the times you should store the direct difference in a separate column to utilize while this would be a display choice only

[Saradomin]

Sorry for the very late response.

Thank you both for your help!