» 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:
- MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb
- Analyzing Markets, Products, and Marketing Plans
- Microsoft Outlook 2002 for Dummies
- Financial Statement Analysis: A Practitioner's Guide, 3rd Edition
- The Basics of Finance: Financial Tools for Non Financial Managers
- Financial Statement Analysis with S&P insert card
No comments have been submitted.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
