aturday, March 16th, 2024 to MM/DD/YY?

[charlesstricklin]

I give up! I've been trying to convert a date, say A2 as Saturday, March 16th, 2024 in a text format, to an actual date in the format of MM/DD/YY?

P.S. Better yet, how to convert date in two cells, say B2 and C2 containing the month, i.e. 3 in the example above, and 16 also in the example above?

 

[dermie_72]

Can you show what the data starts like and what you want it to end as.

 

[Dave Patton]

Format to your preference
You can use find and build the mid function to put information into the Date function or look at the information and compose the function.

T240203a.xlsm
	A	B
1
2	Saturday, March 16th, 2024	45367
3		11
4
2b

Cell Formulas
Range	Formula
B2	B2	=DATE(RIGHT(A2,4),MONTH("1-"&MID(A2,11,5)),MID(A2,17,2))
B3	B3	=FIND(",",A2)+2

 

[Peter_SSs]

See if any of these help.

24 03 18.xlsm
	A	B	C	D	E
1		Date		Month	Day
2	Saturday, March 16th, 2024	16/03/2024		3	16
3	Monday, May 1st, 2023	1/05/2023		5	1
Dates

Cell Formulas
Range	Formula
D2:D3	D2	=MONTH(1&MID(SUBSTITUTE(A2," ",REPT(" ",50)),50,50))
E2:E3	E2	=--MID(A2,FIND(",",A2,11)-4,2)
B2:B3	B2	=DATE(RIGHT(A2,4),MONTH(1&MID(SUBSTITUTE(A2," ",REPT(" ",50)),50,50)),MID(A2,FIND(",",A2,11)-4,2))

 

[charlesstricklin]

See if any of these help.

24 03 18.xlsm
	A	B	C	D	E
1		Date		Month	Day
2	Saturday, March 16th, 2024	16/03/2024		3	16
3	Monday, May 1st, 2023	1/05/2023		5	1
Dates

Cell Formulas
Range	Formula
D2:D3	D2	=MONTH(1&MID(SUBSTITUTE(A2," ",REPT(" ",50)),50,50))
E2:E3	E2	=--MID(A2,FIND(",",A2,11)-4,2)
B2:B3	B2	=DATE(RIGHT(A2,4),MONTH(1&MID(SUBSTITUTE(A2," ",REPT(" ",50)),50,50)),MID(A2,FIND(",",A2,11)-4,2))

The month works spot on, the day and the short date are one more than they should be?

 

[Peter_SSs]

Looks like they ended up working for you.