Automatic Date Update (No Interaction)

[ldcox8551]

We have a file I wish to have displayed on a TV 24/7 in which a counter shows how many days since an event last occurred. The only time interaction with the file would occur would be to update the last event date. Besides that, I am trying to have it where with the file remaining open and with no human interaction will still stay updated as to the current date. I tried making the date an external connection and setting the auto-refresh up on it, but it changes itself from a forumla to the last refresh cycle and locks there until changed again manually. Any thoughts or ideas?

[#2]

@ldcox8551 

Any data source can be set to auto-refresh and almost all data sources have a system time function that can be returned to Excel using MS Query or PowerQuery. Here is the PQ example:

1.  In Excel use menu option: Data > New Query > From other sources > Blank Query
2.  In PQ use menu option Advanced Editor and make this edit
   let
   Source = DateTime.LocalNow
   in
   Source
3. Click Done
4. Click Invoke
5. Click File > Close & Load
6. In Excel click Table Tools Design > Properties
7. Click small icon (connection properties) next to Name:
8. Check Refresh Every and set to 1 minutes
9. Click OK twice to close properties

You now will have the current date updated every minute automatically. You can add formulas to calculate time elapsed from the "Invoked Function" table. 

[#3]

@Craig Hatmaker  

Thank you for the information. I will try this now. 

[#4]

Hi @ldcox8551,

Alternatively, you can add a clock in your spreadsheet with a macro. This will write the current date in cell A1 and current date in cell A2. Since it refresh every second, your spreadsheet will recalculate every second. Execute "SetTime" to start the process and "Disable" to stop the process. 

Dim SchedRecalc As Date

Sub Recalc()

With Sheet1.Range("A1")

.Value = Format(Date)

End With

With Sheet1.Range("A2")

.Value = Format(Time, "hh: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

P.S. I copied this macro from another source. 

[#5]

@Craig Hatmaker 

Worked perfectly. Thank you very much. I've never used Query before and you made it very easy to figure out. 

[#6]

@ldcox8551Glad it worked for you. BTW - I like @Bennadeau's solution. If you need updates quicker than 1 minute, use it. If not, my PQ approach does not require VBA. I love VBA, but then, I'm a coder and not everyone is. VBA can also trigger security alerts. 

[#7]

@Bennadeau 

The other solutions works for what I need but I am curious to learn this method as well for future knowledge. Thank you for sharing. I will play around with this some too.