» Creating a Date and Time Matrix

CATEGORY - Excel Date & Time Formulas VERSION - All Microsoft Excel Versions

Problem:

Listed in column A are the dates and times of doctor's appointments. Column B contains the corresponding patient's name for each appointment.
We want to use this data to create a matrix in cells D1:G10, where each column is a date and each row is a time.

Solution:

Use the INDEX, MATCH, and TEXT functions as shown in the following Array formula:
{=INDEX($B$2:$B$18,MATCH(TEXT(E$1,"mmddyyyy")&TEXT($D2,"hh:mm"),TEXT($A$2:$A$18,"mmddyyyy")&TEXT($A$2:$A$18,"hh:mm"),0))}
Enter the above formula in cell E2, copy it down the column and across to column G.

To apply Array formula:
Select the cell, press and simultaneously press .