Working With Dates In Excel

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.

In the section below, I’ve listed the sample Excel formulas I would use, to calculate specific dates in Excel.

• For all formulas, the date — December 29, 2008 — is in cell A2.

Date Calculation Formulas

Here are the formulas to extract information from a date in cell A2.

Using Calculated Dates in Pivot Table

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 cannot add calculated items to the pivot table

Video: Pivot Table Grouping Tips

This video shows how to group pivot table dates by month and years, and how to group text items manually.

There are written steps, and an Excel workbook, on the How to Group Pivot Table Data page, on my Contextures site.

Pivot Table Grouping

For more information on Excel pivot table grouping, go to the How to Group Pivot Table Data page, on my Contextures site.

There are examples for grouping dates, number and text fields. You’ll also see solutions for fixing pivot table grouping problems, such as the error message, “Cannot group that selection”