Dates can be tricky in Excel, especially in December and January! In those months, you often have to type the year too, because Excel defaults to the current year. There are other Excel date problems too, like dates where you can’t change the formatting, and dates for the GetPivotData function. Here’s a quick look at dealing with those issues.
Year-End Problems with Excel Dates
Maybe I’m the only one who has this problem, but it’s a pain to add the year to Excel dates, in December and January. For most of the year, you just have to type the month and day, then press Enter. Excel automatically adds the current year to the date.
But by mid-December, I’m entering lots of dates for things that will happen in January. When I typed 1/1 and pressed Enter, Excel added 2021 as the year, instead of 2022. Why doesn’t Excel know what I’m thinking?!?
Anyway, now it’s January, and I’m finishing up some December entries. Now I have to type 12/12/21, instead of just 12/12. Otherwise, I’ll end up with a lot of surprise entries for December 2022!
I hope you’re better at remembering this year thing than I am!
Excel Date Functions
To be fair, it’s not ALL problems when you work with dates in Excel.
One of my favourite date functions is the end of month function, EOMONTH. Use it as a quick way find the first or last day of a month.
For example, use these formulas, where you have a date stored in cell B2:
- First day of B2’s month: =EOMONTH(B2),-1)+1
- Last day of B2’s month: =EOMONTH(B2,0)
- First day of next month: =EOMONTH(B2,0)+1
For more Date function tips and examples, go to the Excel Date Formula Examples page on my Contextures site.
Also, there are specialized date formulas on these pages:
Leap Year Calculations
Easter Date Calculations
Nth Weekday in Month (e.g. the 4th Thursday)
Video: Excel Dates Won’t Change Format
See how to fix dates after you import data into Excel, if the dates won’t change format. Usually, this happens because Excel sees the dates as text strings, instead of real date, which are stored as numbers.
To quickly fix the problem, use the Text to Columns feature, which has 3 steps. Then, check the fixed dates, to make sure they are recognized as real dates in Excel
To get the Excel workbook, with the Excel Dates Won’t Change Format example, go to the Excel Dates Fix Format page on my Contextures site
Video Timeline
- 0:00 Introduction
- 0:12 Dates Won’t Change Format
- 0:46 Text to Columns
- 1:03 Step 2
- 1:11 Step 3
- 1:47 Check the Dates
- 2:11 Get the Workbook
Video: Dates in GetPivotData formula
If you’re using dates with a GetPivotData formula, you might have problems with date references. If the date format in the formula is not an exact match for the date format in the pivot table, the result might be an error.
To help you avoid this problem, this short video shows a few Date workarounds for the GetPivotData function.
There are more details and written steps on the GetPivotData page of my Contextures site.
_______________________
How to Fix Problems With Excel Dates
_______________________