Changing date formatting to just month and year

[meleras]

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!

[#2 · Accepted solution]

@meleras 

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.