Mar 24 2021 02:37 PM
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!
Mar 24 2021 03:29 PM - edited Mar 30 2021 06:26 AM
SolutionEnter 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.
Mar 24 2021 03:29 PM - edited Mar 30 2021 06:26 AM
SolutionEnter 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.