Author: Oscar Cronquist Article last updated on October 28, 2018


This blog post describes how to automatically select a value based on time and date using vba in excel.


This workbook is automatically recalculated each minute.

Array formula in cell F4:

=INDEX($B$2:$B$34, MATCH(TEXT($E$2, "yyyy-mm-dd tt:mm"), TEXT($A$2:$A$34, "yyyy-mm-dd tt:mm"), 0)) + CTRL + SHIFT + ENTER

Formula in E2:


Setup recalculation on time intervals

  1. Press Alt-F11 to open vba editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code into a module.
  4. Exit vba editor
Public NextTime As Double

Sub SetTimer()

 Application.OnTime NextTime, "Recalc"

End Sub

Sub Recalc()


 NextTime = Now + TimeValue("00:01:00")


End Sub

If you want ten minutes between recalculations, change TimeValue("00:01:00") to TimeValue("00:10:00")

Start "recalculation on time intervals" when workbook is opened

  1. Press Alt-F11
  2. Doubleclick ThisWorkbook in project window
  3. Select workbook from dropdown list
  4. Copy code below and paste into code window.
Private Sub Workbook_Open()
End Sub