Join Chris Dutton for an in-depth discussion in this video YEARFRAC, part of Excel Workshop: Building a Dynamic Heat Map.
All right. Now that we've created a new field…to calculate the first date of each year,…we have everything we need to translate those dates…in terms of percent through the year.…To do this, we'll use a function called YEARFRAC.…YEARFRAC is a pretty simple Excel function.…Essentially all it does is calculate the fraction of a year…represented by the number of days between two given dates,…so a start date and an end date.…So just a quick word of warning:…when you're using date and time functions in Excel,…especially something like YEARFRAC,…it's important to avoid typing your dates…directly into the formula itself.…
Instead, use references to cells containing dates.…The reason this is important is because…Excel doesn't always recognize date formats.…If you type in a date and Excel reads it as a text string,…it's not going to work the way you expect it to.…One component of the YEARFRAC function is called basis.…The basis option essentially specifies how days are counted.…All right, so let's take a quick look at an example here…
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
Skill Level Intermediate
1. Project Setup
2. Date and Time Functions
3. Conditional Statements and Logical Operators
4. Building a Basic Heat Map
5. Adding Dynamic Filters to the Map
6. Creating Formula-Based Formatting Rules
7. Wrapping Up
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.