Join Chris Dutton for an in-depth discussion in this video Using the NOW and TODAY functions, part of Excel Workshop: Building a Dynamic Heat Map.
- View Offline
- Alright so now it's time to add a real-time element to this tool by highlighting specific rows and columns of our heat map based on the current moment in time. So to do this we're going to use two Excel functions called TODAY and NOW. These are really really simple functions in Excel. Essentially all they do is return the current date and the exact time. So a heads-up when you're working with TODAY and NOW functions, these are two examples of volatile functions in Excel, and what that means is that they update every time you make a workbook change or calculation, and that includes just opening a workbook.
So just keep in mind that these two functions are going to be constantly changing as you work with your workbook. So let's look at a simple example. If I were to open this workbook on October 3rd, 2015 at 4:21 P.M., the TODAY function would return 10/3/2015, the NOW function essentially does the same thing, just at a more granular level. It would return 10/3/2015, along with the timestamp, 16:21. So let's jump over to Excel.
What we want to do is use these two functions to return the current hour and the current day. So in cell M2, let's start by just seeing what the NOW function returns. The NOW function is interesting because it includes an open and closed parentheses with no cell reference within it. That's the entire function. So if I hit enter, and expand this column out, you'll see that it returned 10/13/2015, 11:01 A.M.
so that's the NOW function. What I need to do is just extract the hour out of that function so what I can do is wrap that NOW function in an hour formula and when I do that, it just returns the hour, which when I format the cells, it will become more clear, is the 11th hour of the day. So we'll do a very similar thing with the TODAY function. So again, if I start with just TODAY, open-close parentheses, return the current date 10/13/2015 and again similar story here.
All I want is the weekday of TODAY, don't need the whole date. So I'm going to wrap the function with a weekday formula, and then I'll reformat so that it becomes more clear. That returns a 3, which tells me it's the third day of the week, which is a Tuesday, which is correct. So now I have the tools that I need to introduce that real-time element to highlight specific data within the heat map itself. So the Key Takeaway here, the TODAY and NOW functions will allow us to create real-time formatting rules based on the current hour and day.
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