Convert text formatted date to date format

[susom]

How do I format this text "Nov-14-2022" to date format? I have tried the DATEFORMAT function to no avail--it results in a #VALUE!

Background:
I have data that coming out of a screen query that has a long string of text with a date range imbedded in it. This is the format: " Nov-14-2022 to Jan-09-2023". I am able to use text functions to pull the begging and ending dates in this format: "Nov-14-2022" and "Jan-09-2023" in two separate cells. I would now like to calculate the number of days between the two dates, but as they are formatted as text I cannot. Feels like it should be easy, but a simple google search provided now help.

 

[Georgiboy]

The method will depend on which version of Excel you are using?

Also are you looking to do this with VBA or Formula?

 

[susom]

Excel version 2302. A formula would be ideal.

 

[Tetra201]

To convert your text string into a date value, try

=--SUBSTITUTE(B2,"-",", ",2)

To calculate the number of days between two dale-like text strings, try

=SUBSTITUTE(C2,"-",", ",2)-SUBSTITUTE(B2,"-",", ",2)

 

[susom]

HA! Thank you. Your solution is so much simpler that this cob job set of formulas that I finally managed to make work.

=CONCATENATE((VLOOKUP(MID((LEFT((RIGHT(F9,(LEN(F9))-(FIND("e: ",F9)+2))),11)),1,3),$K$9:$L$20,2,FALSE))&"/"&MID((LEFT((RIGHT(F9,(LEN(F9))-(FIND("e: ",F9)+2))),11)),5,2)&"/"&MID((LEFT((RIGHT(F9,(LEN(F9))-(FIND("e: ",F9)+2))),11)),8,4))

 

[Georgiboy]

Another option that won't be restricted by locale,

Book1
	A	B
1	Nov-14-2022	14/11/2022
2	Jan-09-2023	09/01/2023
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(RIGHT(A1,4),MONTH("1"&LEFT(A1,3)),MID(A1,5,2))