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.
=TRIM(IF(ABS(INT(B1-A1))=0,"",ABS(INT(B1-A1))&" Days")&IF(HOUR(MOD(B1-A1,1))>0," "&HOUR(MOD(B1-A1,1))&" Hours","")&IF(MINUTE(MOD(B1-A1,1))>0," "&MINUTE(MOD(B1-A1,1))&" Minutes","")&IF(SECOND(MOD(B1-A1,1))>0," "&SECOND(MOD(B1-A1,1))&" Seconds",""))
It can be much smaller if you are good with results that always display the parts...
3 days 0 hours 2 minutes 12 seconds
=INT(B1-A1)&" Days "&HOUR(MOD(B1-A1,1))&" Hours "&MINUTE(MOD(B1-A1,1))&" Minutes "&SECOND(MOD(B1-A1,1))&" Seconds"
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
Bookmarks