Introduction

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

Example,

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:

=NOW()+ ENTER

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()
 Calculate
 NextTime = Now + TimeValue("00:01:00")
 SetTimer
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()
 Recalc
End Sub

Related posts:

Count entries between date and time criteria in excel

Calculate dates in each step in a project based on a finish date

Excel: Count time

Copy a cell range whose size is likely to change from time to time (vba)

Highlight specific time ranges in a weekly schedule in excel