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!

Let's take the ER appointment example to demonstrate how Excel can analyze this data.

- Admission Id (a running number)

- Patient Name

- Problem (fever, head wound, etc)

- Time admitted

- Time released

- 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!

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