Formatting Date and Time in cell while also counting down days?

[jmpatrick]

Here's my sheet:

Here's the formula in E56:

=C56-D56

Here's the formatting in E56:

d "Days" h "Hours" m "Minutes" ss "Seconds"

How can I show the "Days" number as the number of days until the deadline? So it should look like this:

145 Days 22 Hours 15 Minutes 56 Seconds

It should switch from 145 Days to 144 when it rolls over.

 

[jasonb75]

I think that the only way you can do it is to split the value and show it as a string with the text function.

Formula looks right but I'm not able to test it at present.

=INT(C56-D56)&" days "&TEXT(MOD(C56-D56,1),"h"" hours ""m"" minutes ""s"" seconds""")

 

[etaf]

are you expecting the countdown to occur automatically - with a function it will only update if the sheet calculates using F9
you will need some sort of action to trigger the calc - so maybe VBA - which i'm sorry - but i cannot provide

different cells - but using jasonb75 formula for you to see

Book2
	A	B	C	D	E
1	1/31/23 8:00	1/3/23 23:18		27 days 8 hours 41 minutes 4 seconds
Sheet3

Cell Formulas
Range	Formula
B1	B1	=NOW()
D1	D1	=INT(A1-B1)&" days "&TEXT(MOD(A1-B1,1),"h"" hours ""m"" minutes ""s"" seconds""")

 

[jmpatrick]

I found a workable solution that required some different coding. Here's the formula I used to subtract the current date and time from the deadline date and time:

=DATEDIF(B1,B2,"d")& " Days  "&TEXT(B2-B1,"h")&" Hours  "&TEXT(B2-B1,"m")&" Minutes  "&TEXT(B2-B1,"s")&" Seconds"

...and here's the vba used to refresh the current date and time. I call Recalc when the Workbook is opened and Disable when it is closed.

Dim SchedRecalc As Date
Sub Recalc()
With Range("CurrentDateTime")
.Value = Format(Date, "m/d/yyyy") & " " & Format(Time, "h:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

Thanks for everyone's help and I hope this is useful to someone else in the future.

 

[jmpatrick]

Slight correction to the formula above. Should be this so that minutes are correct:

=DATEDIF(B1,B2,"d")& " Days  "&TEXT(B2-B1,"h")&" Hours  "&RIGHT(TEXT(B2-B1,"hh: m"),2)&" Minutes  "&TEXT(B2-B1,"s")&" Seconds"

 

[jasonb75]

Or you could use the one that I suggested instead of making it more complicated than necessary

 

[jmpatrick]

Or you could use the one that I suggested instead of making it more complicated than necessary

Both give the desired result. Yours's is more concise so that's the one I'll use. Thanks.