Excel Date Formulas Simplified – A Case Study

Overview

Many Excel users are downright scared of date formulas. At one point in my Excel career I was one of those users. But since dates are found in most Excel workbooks, I had to overcome my fears. And you know what? Date formulas aren’t half as scary as I thought they were.

In this article I hope to show you how simple and useful Excel date formulas can be. In our case study, you’ll see how by using a single date function (the MONTH() function) and some filtering we can accomplish real-world tasks like keeping track of impending birthdays of employees.

Using the MONTH() function in an Excel Formula

So here’s a list of some of the employees of the fictitious XYZ widget company:

We’ll use the MONTH() function for two separate tasks. The first is to find the current month. We do this with the formula =MONTH(TODAY()).

The TODAY() function returns the current date, we then take this date and extract the ‘month part’ from it by using the MONTH function. And the result is the current month.

Cell C2 in the screen shot below holds the result.

The second task for the MONTH() function is to calculate the month in which each employee was born. We’ll do this by using the MONTH function to return the month part of each employee’s date of birth. This is accomplished by doing the following:

• We will select cell D5, and type in the following formula: =MONTH(C5).
• And then we will copy the formula to all the cells in the D column.

Note: This time we used the MONTH() function to extract the month part of the employees birth-dates. These dates are found in column C and column D will now contain the month of birth for each person. In our sample, it’s 1 for John, 1 for Roger, 12 for Ananias, and so on:

Matching the Criteria

So, now we have all the data we need: the current month and the birth month of each person. The final step is to check out if they’re the same:

• We’ll select cell E5 and type in: =IF(D5=$C$2,”YES”,”NOPE”).
• And copy the formula to all the table cells in the E column.

Each cell will display either YES if that person’s birthday is this month, or NOPE if it isn’t:

And we can now easily filter only the employees that have a birthday this month using Excel’s Filter functionality:

Summary

In this article, we discussed a real-world use-case for the MONTH() and TODAY() functions. I hope that by now you see that date formulas can be simple to use and understand. I’ll leave you with a puzzle:

Instead of creating the ‘Month of Birth’ column and the ‘Current Month’ cell, we could have used the following formula in the ‘Birthday This Month’ column:

=IF(MONTH(C5)=MONTH(TODAY()), “YES”, “”)

Can you figure out why it works?