Sep 12 2019 02:50 PM
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/2019 | 49/2019 |
50/2019 | 50/2019 |
51/2019 | 51/2019 |
52/2019 | 52/2019 |
43831 | 01/2020 |
43862 | 02/2020 |
43891 | 03/2020 |
43922 | 04/2020 |
43952 | 05/2020 |
43983 | 06/2020 |
44013 | 07/2020 |
44044 | 08/2020 |
44075 | 09/2020 |
44105 | 10/2020 |
44136 | 11/2020 |
44166 | 12/2020 |
13/2020 | 13/2020 |
14/2020 | 14/2020 |
15/2020 | 15/2020 |
Sep 12 2019 04:17 PM - edited Sep 12 2019 04:26 PM
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))