How can I easily format "202104" --> 2021/04 in Excel?
My current method is to concatenate the original string with "01" and then change it into a date. However, I am seeking a more efficient format method.
Thanks
Asked
Active
Viewed 51 times
0
-
Do you need it to be an actual date, or just text, or a formatted number? If just formatted, a custom format of 0000"/"00 would do. For text, you can use the `TEXT` function with the same format. For a real date, your current way is as good as any. – Rory Apr 27 '21 at 12:05
-
I need it as an actual date. Thanks for the help! – Qwerty Apr 27 '21 at 12:32
-
Then depending on how you "change it to a date", I don't really see what alternative there is. – Rory Apr 27 '21 at 12:42
2 Answers
0
Try this.
For Text
=LEFT(A1,4)&"/"&RIGHT(A1,2)
For Date Value
=TEXT(DATEVALUE(LEFT(A1,4)&"/"&RIGHT(A1,2)),"YYYY/MM")
Rajput
- 605
- 3
- 12
-
Thanks, the Text version works well. However, I get a Value error with the second attempt. – Qwerty Apr 27 '21 at 12:34
-
1@qwerty you replied to a comment on your Q _I need it as an actual date_. These returns a string, not a date – chris neilsen Apr 27 '21 at 15:24
0
As far as I know, 2021/04 is not a valid date in Excel, but 2021/04/01 (first of April, year 2021) is.
In order to achieve this, you can use this formula:
=DATE(INTEGER(202104 / 100);MOD(202104;100);1)
Where:
1) INTEGER(202104/100) is the integer division of 202104 by 100, calculating the year.
2) MOD(202104;100) means 202104 modulo 100, in order to calculate the month.
3) 1 means the first day of the month.
