Ask Your Question

Revision history [back]

You may use square brackets for most significant part of your time format: [HH]:MM:SS, or [MM]:SS, to show time like "12300:00:21" or "738000:21".

You may use the fact that date/time is actually a float that measures in days, i.e. whole part is number of days, and fractional part is fraction of day (i.e. time), so 0.5 is 12:00 AM (noon). So, a formula like =TRUNC(A1)&" days "&TEXT(MOD(A1,1),"HH \h\r MM \m\i\n SS \s\e\c") might do the job. This creates a string indeed, but in this case, you would probably want to keep time spans in a different (hidden?) column which were used for further calculations, and this formula would serve the purpose of displaying data to user.