SOLVED

Changing date formatting to just month and year

%3CLINGO-SUB%20id%3D%22lingo-sub-2233617%22%20slang%3D%22en-US%22%3EChanging%20date%20formatting%20to%20just%20month%20and%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2233617%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20got%20dates%20over%2020%20years%20arranged%20by%20month%2C%20like%20so%3A%3C%2FP%3E%3CTABLE%20width%3D%22112%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22112%22%3EJan-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFeb-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMar-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EApr-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMay-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJun-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJul-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAug-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOct-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENov-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDec-00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Jan%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Feb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Mar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Apr%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-May%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Jun%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Jul%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Aug%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Sep%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Oct%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Nov%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Dec%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Jan%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Feb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Mar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Apr%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-May%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Jun%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Jul%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Aug%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Sep%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Oct%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Nov%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02-Dec%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Jan%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Feb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Mar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Apr%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-May%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Jun%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Jul%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Aug%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Sep%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Oct%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Nov%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03-Dec%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04-Jan%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04-Feb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04-Mar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04-Apr%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04-May%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04-Jun%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI.e%2C%20that's%20January%202000%20to%20June%202004.%20Excel%20is%20reading%20the%20month%20as%20a%20day%2C%20however%2C%20so%20it's%20giving%20me%20everything%20past%202000%20as%20being%20in%202021.%20I.e%2C%20I've%20got%20Jan%201st%2C%202021%2C%26nbsp%3B%20Feb%201st%2C%202021%2C%20March%201st%202021%2C%20etc%2C%20instead%20of%20Jan%202001%2C%20Feb%202001%2C%20March%202001.%20How%20can%20I%20get%20it%20to%20not%20read%20the%20day%2C%20and%20only%20go%20month%20and%20year%3F%20There's%20no%20option%20for%20it%20when%20I%20press%20ctrl-1%2C%20even%20in%20the%20custom%2C%20which%20does%20have%20a%20few%20more%20date%20options.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2233617%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2233705%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20date%20formatting%20to%20just%20month%20and%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2233705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1007805%22%20target%3D%22_blank%22%3E%40meleras%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%201-Jan-2000%20in%20the%20first%20cell.%3C%2FP%3E%0A%3CP%3EWith%20this%20cell%20selected%2C%20apply%20the%20custom%20number%20format%20yy-mmm%20so%20that%20it%20displays%20as%2000-Jan.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Fill%20%26gt%3B%20Series.%3C%2FP%3E%0A%3CP%3ESelect%20Columns%2C%20select%20Month%2C%20and%20set%20the%20Stop%20value%20to%201-Jun-2004.%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0238.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266834i2096256CE418B97E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0238.png%22%20alt%3D%22S0238.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

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!

1 Reply
best response confirmed by meleras (Frequent Visitor)
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.

 

S0238.png