Excel Video 482 Building a Date Table

Excel Video 482 explains the rationale for building a date table in the Excel Data Model. Remember that tools like PowerPivot and Power View don’t allow you to group date fields like you can group in a traditional Pivot Table. Our objective today is building a date table that does the grouping for you. Once you have the date table built, create a relationship (see Excel Video 477) between the date table and the date field in your PM or EHR data (DOS, Date of Entry, Appointment Date, etc.). You can then use all of the columns in your date table to analyze, pivot, and chart that date in Excel’s BI tools. Remember from Excel Video 480 why and how to use the sorting fields in the date table.

Here are some sample formulas to get you started building a date table. Replace cell A2 in the formulas with your date column as necessary.

Year=YEAR(A2)

Quarter=”Q”&ROUNDUP(MONTH(A2)/3,0)

Month=TEXT(A2, “MM – mmmm”)

MonthNumber=MONTH(A2)

MonthShort=TEXT(A2,”mmm”)

MonthLong=TEXT(A2,”mmmm”)

Day=DAY(A2)

Weekday=TEXT(A2,”dddd”)

WeekdayShort =TEXT(A2,”ddd”)

WeekdaySort=WEEKDAY(A2)

MonthYear=TEXT(A2,”mmm”)&” “&YEAR(A2)

YearMonthSort=YEAR(A2)*100+MONTH(A2)