Separate date (mm/dd/yyyy) into 3 separate columns (MM, DD, and YYYY).

[bob77044]

I have one column in Excel 2019 containing a normal date (MM/DD/YYYY). I need to separate that date into three columns; one for MM, one for DD, and one for YYYY. I've tried to use the "/" as a separator in the text-to-columns, but that's not working for me. Any suggestions on how I might accomplish this? I'm using Win 10 and Excel 2019.

[6StringJazzer]

You can do it with formulas that refer to the first column. Text to columns doesn't work because column A is not text.

If you want to get rid of column A, you have two options.

You can copy B:D and Paste Values then delete A
You can copy A to B and Paste Values, then update the formulas for C and D to refer to B, then delete A.

Formulas shown below.

Values as displayed

	A	B	C	D	E
1	Date	MM	DD	YYYY	Description
2	10/21/2020	10	21	2020	Walmart
3	03/15/2021	03	15	2021	Di Iorio Farms
4	12/31/2020	12	31	2020	TRTA
5	10/30/2019	10	30	2019	DAV
6	08/13/2020	08	13	2020	Paralyzed Veterans
7	04/29/2021	04	29	2021	Boys Town
8	01/19/2018	01	19	2018	Wings 'n More

Underlying formulas

	A	B	C	D	E
1	Date	MM	DD	YYYY	Description
2	44125	=TEXT(A2,"MM")	=TEXT(A2,"DD")	=TEXT(A2,"YYYY")	Walmart
3	44270	=TEXT(A3,"MM")	=TEXT(A3,"DD")	=TEXT(A3,"YYYY")	Di Iorio Farms
4	44196	=TEXT(A4,"MM")	=TEXT(A4,"DD")	=TEXT(A4,"YYYY")	TRTA
5	43768	=TEXT(A5,"MM")	=TEXT(A5,"DD")	=TEXT(A5,"YYYY")	DAV
6	44056	=TEXT(A6,"MM")	=TEXT(A6,"DD")	=TEXT(A6,"YYYY")	Paralyzed Veterans
7	44315	=TEXT(A7,"MM")	=TEXT(A7,"DD")	=TEXT(A7,"YYYY")	Boys Town
8	43119	=TEXT(A8,"MM")	=TEXT(A8,"DD")	=TEXT(A8,"YYYY")	Wings 'n More

[josephteh]

Alternatively, MM=MONTH(A2), DD=DAY(A2) and YYYY=YEAR(A2).

[6StringJazzer]

Alternatively, MM=MONTH(A2), DD=DAY(A2) and YYYY=YEAR(A2).

You can do this and you would also have to update the formatting of the month and day cells to include leading 0's.

[josephteh]

Thanks Jeff.. OP has already format the cells as "00".

[bob77044]

A BIG thank you to both josephteh and 6StringJazzer for their quick reply!

[FlameRetired]

Late to the party again.

Another way. In F2 filled down and across.

Please Login or Register  to view this content.

and format cells as you noted above.

	A	B	C	D	E	F	G	H
1	Date	MM	DD	YYYY	Description	MM	DD	YYYY
2	10/21/2020	10	21	2020	Walmart	10	21	2020
3	03/15/2021	03	15	2021	Di Iorio Farms	03	15	2021
4	12/31/2020	12	03	2020	TRTA	12	31	2020
5	10/30/2019	10	30	2019	DAV	10	30	2019
6	08/13/2020	08	13	2020	Paralyzed Veterans	08	13	2020
7	04/29/2021	04	29	2021	Boys Town	04	29	2021
8	01/19/2018	01	19	2018	Wings 'n More	01	19	2018