Forum Discussion
Changing date formatting to just month and year
I've got dates over 20 years arranged by month, like so:
| Jan-00 |
| Feb-00 |
| Mar-00 |
| Apr-00 |
| May-00 |
| Jun-00 |
| Jul-00 |
| Aug-00 |
| Sep-00 |
| Oct-00 |
| Nov-00 |
| Dec-00 |
| 01-Jan |
| 01-Feb |
| 01-Mar |
| 01-Apr |
| 01-May |
| 01-Jun |
| 01-Jul |
| 01-Aug |
| 01-Sep |
| 01-Oct |
| 01-Nov |
| 01-Dec |
| 02-Jan |
| 02-Feb |
| 02-Mar |
| 02-Apr |
| 02-May |
| 02-Jun |
| 02-Jul |
| 02-Aug |
| 02-Sep |
| 02-Oct |
| 02-Nov |
| 02-Dec |
| 03-Jan |
| 03-Feb |
| 03-Mar |
| 03-Apr |
| 03-May |
| 03-Jun |
| 03-Jul |
| 03-Aug |
| 03-Sep |
| 03-Oct |
| 03-Nov |
| 03-Dec |
| 04-Jan |
| 04-Feb |
| 04-Mar |
| 04-Apr |
| 04-May |
| 04-Jun |
I.e, that's January 2000 to June 2004. Excel is reading the month as a day, however, so it's giving me everything past 2000 as being in 2021. I.e, I've got Jan 1st, 2021, Feb 1st, 2021, March 1st 2021, etc, instead of Jan 2001, Feb 2001, March 2001. How can I get it to not read the day, and only go month and year? There's no option for it when I press ctrl-1, even in the custom, which does have a few more date options.
Thanks!
Enter 1-Jan-2000 in the first cell.
With this cell selected, apply the custom number format yy-mmm so that it displays as 00-Jan.
On the Home tab of the ribbon, select Fill > Series.
Select Columns, select Month, and set the Stop value to 1-Jun-2004.
Click OK.
1 Reply
Enter 1-Jan-2000 in the first cell.
With this cell selected, apply the custom number format yy-mmm so that it displays as 00-Jan.
On the Home tab of the ribbon, select Fill > Series.
Select Columns, select Month, and set the Stop value to 1-Jun-2004.
Click OK.