This week I’ve been working on date formulas, from very simple ones, to complex formulas that calculate workdays per month, based on start and end dates that can span several months.
Extract Information from a Date
Many times I need to pull a bit of information from a date, such as the year, month or weekday.
Here are the sample formulas I’d use. The date — December 29, 2008 — is in cell A2.
To Calculate |
The Formula |
The Result |
Year | =YEAR(A2) | 2008 |
Month Number | =MONTH(A2) | 12 |
Month Name (short) | =TEXT(A2,”mmm”) | Dec |
Month Name (long) | =TEXT(A2,”mmmm”) | December |
Day of the month | =DAY(A2) | 29 |
Weekday Number | =WEEKDAY(A2,1) | 2 |
Weekday Name (short) | =TEXT(A2,”ddd”) | Mon |
Weekday Name (long) | =TEXT(A2,”dddd”) | Monday |
Year Month | =TEXT(A2,”yyyy mm”) | 2008 12 |
Using the Calculated Dates
If I plan to create a pivot table from data that contains a date field, I usually calculate the year and month in the source data. Then I can add those fields to the pivot table, instead of the individual dates.
Yes, the pivot table could automatically group the individual dates by year and month, but that can limit other functions in the pivot table. For example:
- if two pivot tables are based on the same data, grouping one pivot table by month would cause the other pivot table to also be grouped by month.
- if a field is grouped, you can’t add calculated items to the pivot table