Forum Discussion
Converting Date to Week/Year Format
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 |
3 Replies
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))SergeiBaklanthis worked, thank you!
abutula1988 , you are welcome
