» Creating a Date and Time Matrix
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- 422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Monte Carlo Methods in Finance
- Analyzing Markets, Products, and Marketing Plans
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Retire Young, Retire Rich
Simon
- that's Ctrl + Shift + Enter at the end, btw.
