Date and time based data is a very common feature of many Microsoft Excel spreadsheets. Your time-related information might include project management deadlines, meeting start and end times, admission dates to the ER and so on. Sometimes it gets complicated and confusing when it comes to analyzing this data, however...
In this article I will show you an example of how one can analyze time related data in Microsoft Excel spreadsheets while avoiding resorting to the confusing date and time math!
Working Example
Let's take the ER appointment example to demonstrate how Excel can analyze this data.
We will make a table with a list of daily ER admissions, with the following columns:
- Admission Id (a running number)
- Patient Name
- Problem (fever, head wound, etc)
- Time admitted
- Time released
To analyze this data we would need the following formulas:
- How many patients were admitted.
- How many patients are admitted in the evening versus the morning.
Our first task is to count the number of people admitted with various problems. This is easy enough, we simply use COUNTIF.
=COUNTIF(C$2:C$101,G2)
This counts occurrences in a given range that match the problem name found in column G.
We can then extend this to discover the number of people who were admitted in the AM or PM with the COUNTIFS function:
=COUNTIFS(C$2:C$101,G2,$D$2:$D$101,">=12:00 PM")
So this counts those where the problem matches AND where the admittance time is greater or equal to 12:00 PM. Easy!
Summary
Hopefully this simple example will demonstrate that while time math is tricky and confusing sometimes to do manually, it is very easy and straightforward when you get Excel to do the heavy lifting!
In this article I will show you an example of how one can analyze time related data in Microsoft Excel spreadsheets while avoiding resorting to the confusing date and time math!
Working Example
Let's take the ER appointment example to demonstrate how Excel can analyze this data.
We will make a table with a list of daily ER admissions, with the following columns:
- Admission Id (a running number)
- Patient Name
- Problem (fever, head wound, etc)
- Time admitted
- Time released
To analyze this data we would need the following formulas:
- How many patients were admitted.
- How many patients are admitted in the evening versus the morning.
Our first task is to count the number of people admitted with various problems. This is easy enough, we simply use COUNTIF.
=COUNTIF(C$2:C$101,G2)
This counts occurrences in a given range that match the problem name found in column G.
We can then extend this to discover the number of people who were admitted in the AM or PM with the COUNTIFS function:
=COUNTIFS(C$2:C$101,G2,$D$2:$D$101,">=12:00 PM")
So this counts those where the problem matches AND where the admittance time is greater or equal to 12:00 PM. Easy!
Summary
Hopefully this simple example will demonstrate that while time math is tricky and confusing sometimes to do manually, it is very easy and straightforward when you get Excel to do the heavy lifting!
Comments