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



## jmpatrick (Jan 3, 2023)

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 (Jan 3, 2023)

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 (Jan 3, 2023)

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

Book2ABCDE11/31/23 8:001/3/23 23:1827 days 8 hours 41 minutes 4 secondsSheet3Cell FormulasRangeFormulaB1B1=NOW()D1D1=INT(A1-B1)&" days "&TEXT(MOD(A1-B1,1),"h"" hours ""m"" minutes ""s"" seconds""")


----------



## jmpatrick (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

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


----------



## jmpatrick (Jan 5, 2023)

jasonb75 said:


> 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.


----------

