Converting Date to Week/Year Format

Copper 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