Home

Converting Date to Week/Year Format

%3CLINGO-SUB%20id%3D%22lingo-sub-853518%22%20slang%3D%22en-US%22%3EConverting%20Date%20to%20Week%2FYear%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853518%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20output%20from%20SAP%20that%20produces%20the%20column%20in%20the%20left%20and%20I%20am%20looking%20for%20a%20formula%20that%20will%20get%20it%20to%20format%20like%20the%20column%20on%20the%20right%2C%20so%20when%20I%20create%20a%20pivot%20table%20the%20ww%2Fyyyy%20formated%20dates%20are%20in%20the%20correct%20order.%20Excel%20thinks%20that%2001%2F2020%20means%20January%202020%2C%20but%20in%20this%20case%20it%20is%20actually%20the%20first%20week%20of%20the%202020%20year.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E49%2F2019%3C%2FTD%3E%3CTD%3E49%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E50%2F2019%3C%2FTD%3E%3CTD%3E50%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E51%2F2019%3C%2FTD%3E%3CTD%3E51%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E52%2F2019%3C%2FTD%3E%3CTD%3E52%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43831%3C%2FTD%3E%3CTD%3E01%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43862%3C%2FTD%3E%3CTD%3E02%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43891%3C%2FTD%3E%3CTD%3E03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43922%3C%2FTD%3E%3CTD%3E04%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43952%3C%2FTD%3E%3CTD%3E05%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43983%3C%2FTD%3E%3CTD%3E06%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E44013%3C%2FTD%3E%3CTD%3E07%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E44044%3C%2FTD%3E%3CTD%3E08%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E44075%3C%2FTD%3E%3CTD%3E09%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E44105%3C%2FTD%3E%3CTD%3E10%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E44136%3C%2FTD%3E%3CTD%3E11%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E44166%3C%2FTD%3E%3CTD%3E12%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F2020%3C%2FTD%3E%3CTD%3E13%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%2F2020%3C%2FTD%3E%3CTD%3E14%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%2F2020%3C%2FTD%3E%3CTD%3E15%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-853518%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853618%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20Date%20to%20Week%2FYear%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408200%22%20target%3D%22_blank%22%3E%40abutula1988%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20you%20sample%20it's%20not%20clear%20how%20data%20are%20formatted%20in%20left%20column.%20Assuming%20converted%20to%20mm%2Fyyyy%20dates%20are%20not%20formatted%20back%20to%20text%2C%20formula%20could%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(ISTEXT(A1)%2C%20A1%2C%20TEXT(MONTH(A1)%2C%2200%22)%20%26amp%3B%20%22%2F%22%20%26amp%3B%20YEAR(A1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854430%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20Date%20to%20Week%2FYear%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3Ethis%20worked%2C%20thank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855095%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20Date%20to%20Week%2FYear%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408200%22%20target%3D%22_blank%22%3E%40abutula1988%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
abutula1988
New Contributor

I have an output from SAP that produces the column in the left and I am looking for a formula that will get it to format like the column on the right, so when I create a pivot table the ww/yyyy formated dates are in the correct order. Excel thinks that 01/2020 means January 2020, but in this case it is actually the first week of the 2020 year. 

 

49/201949/2019
50/201950/2019
51/201951/2019
52/201952/2019
4383101/2020
4386202/2020
4389103/2020
4392204/2020
4395205/2020
4398306/2020
4401307/2020
4404408/2020
4407509/2020
4410510/2020
4413611/2020
4416612/2020
13/202013/2020
14/202014/2020
15/202015/2020
3 Replies

@abutula1988 

From you sample it's not clear how data are formatted in left column. Assuming converted to mm/yyyy dates are not formatted back to text, formula could be

 

=IF(ISTEXT(A1), A1, TEXT(MONTH(A1),"00") & "/" & YEAR(A1))

 

 

@Sergei Baklanthis worked, thank you! 

@abutula1988 , you are welcome

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies