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 date 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 the current year, instead of next year. 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 1month/day/last year, instead of just month/day. Otherwise, I’ll end up with a lot of surprise entries for last December!
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 the following 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.
Special Date Formulas
Also, there are specialized date formulas on these pages:
Nth Weekday in Month (e.g. the 4th Thursday)
Video: Excel Dates Won’t Change Format
Sometimes, after you import data into Excel, 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.
In the video below, I show how you can quickly fix that problem, using the Text to Columns feature, which has 3 steps.
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.
In the short video below, I show how you can avoid this problem, using 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
_______________________
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 the current year, instead of next year. 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 1month/day/last year, instead of just month/day. Otherwise, I’ll end up with a lot of surprise entries for last December!
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 the following 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.
Special Date Formulas
Also, there are specialized date formulas on these pages:
Nth Weekday in Month (e.g. the 4th Thursday)
Video: Excel Dates Won’t Change Format
Sometimes, after you import data into Excel, 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.
In the video below, I show how you can quickly fix that problem, using the Text to Columns feature, which has 3 steps.
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.
In the short video below, I show how you can avoid this problem, using 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
_______________________
When type 1/1 then excel complete it 1/1/21 ,, is a general phenomenon ,, since assumes the current year,, since was the mid of December 21 then completed the date 1/1/21 ,, format depands on regional settings. If is 4 digits year then 1/1/2021.
Now type 1/1 u get 1/1/22.
Have you considered using WORKDAY.INTL for Nth day calculations?
=WORKDAY.INTL(DATE(2018,11,0),4,”1110111″)
Thanks, David, and that’s a brilliant solution! I’ve added it to my Nth Day page, with credit to you:
https://www.contextures.com/exceldatenthdaymonth.html#intl
Hi David.
This is so elegant and creative!
Was not aware of using the Date() function to find the last day of month. Very short, great.
And how does your brain work to invent a week having only one working day? 🙂
Awesome.
Cheers, Hugo.
I can’t take all the credit. I host an Excel User Group where I work, and it was a member of that group who originally commented on using the trick of a workday string.
It’s a great trick, so thanks for sharing it!
Using Debra’s named ranges and generating the string ”1110111″ you’ll be more flexible:
Thanks, Hugo! I’ve added your suggestion for David’s formula to my Nth Day page:
https://www.contextures.com/exceldatenthdaymonth.html#intl
Way back in 2015, I wrote about WORKDAY.INTL, and showed how to use CONCATENATE to build the string.
It’s easier now, with CONCAT!
https://contexturesblog.com/archives/2015/12/10/customize-weekends-with-excel-workday-function/
Thanks Debra for keeping your Contextures site going in your retirement. It is so incredibly valuable! I was struggling for a while trying to convert imported dates i.e. Dec 1, 2012, 1:23 PM and Dec 1, 2012, 11:23 PM and Dec 10, 2012, 1:23 PM and Dec 10, 2012, 11:23 PM to a date format I could use. You hit the nail on the head with your Text to Columns advice. With concatenating, copy paste and formatting, I could finally sort the dates by year instead of month!
THANK YOU!
Thanks, Dave, and I’m glad the Text to Columns advice fixed your date problem!