Join Chris Dutton for an in-depth discussion in this video EOMONTH, part of Building a Dynamic Heat Map in Excel.
- Okay so you may have realized that we took a bit of a shortcut there when we're calculating season by basing season on months. Now, when realistically we know that Spring doesn't start officially on March 1. It starts on March 20, which is the Spring Equinox. So redefining season based on the exact date is much trickier but we can accomplish it using some clever date and time functions. So rather than defining season based on the month, we're going to define season based on fractions of the year.
So if we know that Spring technically starts on March 20, we can use functions to say March 20 represents 21.6 percent of the way through the year so we know that any date falling within that range should be categorized as Spring and to do this, we're going to use two different functions, EOMONTH and YEARFRAC so let's start by taking a deep dive into the EOMONTH function. So the EOMONTH function returns the last day of a given month.
It can also be used to calculate the start or end dates of previous or future months as well. And when you use the EOMONTH function that requires a reference called months where zero will return the last date of the current month while a positive or negative value will return the last date of a future or past month. So let's take a look at some examples here. We have the date, October 3, 2015. There are a number of ways we can use EOMONTH function here. To calculate the end of the current month, we can use the function with a months reference of zero which returns the date 10/31/2015.
If we want the last date of the next month, we can replace that months reference of zero with a one. If we want the last date of the previous month, we can replace that with a negative one and if we want the first date of a given month, all we need to do is add one at the end of the function. So looking at this last example here, we're calculating the last date of the previous month which is September 30, 2015 and then adding one date to that, which ends up with 10/1/2015.
We're going to use the EOMONTH function in our raw data set to calculate the first date of each given year which we'll use as out starting point to calculate percent through the year to redefine season. So let's jump into Excel and I''m going to insert a new column here to the left of column C and I'm going to call it First of Year and here's where I'm going to insert my EOMONTH function. So let's just start typing it out.
Equals EOMONTH and my start date is going to be the date reference in B2. Now here's where it gets a little bit trickier because I'm not just looking for the first or last date of the current month. I'm looking for the first date of the current year which means then I need to reference a certain number of months in the past and then add one day to get the first date of the next month. So in this case I'm looking at a date that's in January so my months reference in this case would be negative one and then I would close the parentheses and add one day to it which would return exactly what I need.
1/1/2010 The problem is that this won't work for dates from other months so if I were to apply this down, you'll see that it's functioning properly for January but as soon as I get into February dates, 2/1/2010 is not the first date of the year so I need to make this months reference dynamic and to do that I'm going to use the months function that we showed you in one of the first sections. So rather than hard coating a negative one here, we use the month function to return the month of whatever date I'm currently looking at times negative one because I want to look in the past, that number of months.
And now when I hit Enter and apply this down, you'll see that we get the first date from each given year so if you scroll down and take a look, the only thing changing is the year. 2011 2012 2013 So there you go. That's the EOMONTH function to calculate the first date of each given year. So the key takeaway here is that we used EOMONTH to return the first day of the year for each date in our sample which we'll then combine with YEARFRAC to show dates in terms of percent through the year.
Chris Dutton provides hands-on examples designed to showcase why certain functions—DATEVALUE, VLOOKUP, COUNTIF, and more—are so valuable to mapping data. He also shows how to use conditional color-scale formatting to map your data matrix, and find different ways to examine the same data with different criteria, using dynamic filters. The course wraps up with a few finishing touches to make your heat map even more useful and engaging, including a preview of what's possible with the Power Map addin.
- Setting up the heat map project
- Using functions such as WEEKDAY and VLOOKUP
- Adding conditional statements
- Formatting with the color-scale feature
- Aggregating data with SUM
- Adding dynamic filters
- Creating formula-based formatting rules
- Adding additional analytics