Date Formula

[SOLTEC]

My bank sends me statements with the date format like this

Thu, Jan 04 2024 and Excel won't convert it.


Is there a formula to allow me to convert this to either a Long date Thu, Jan. 04, 2024, OR
to convert it to short date 01/04/2024. Thank you in advance.

 

[Joe4]

For a value in cell A1, you can use a formula like this to convert it to a valid date value:

=DATEVALUE(MID(A1,6,6) & ", " & RIGHT(A1,4))

and then you can apply any date format you want to it.

 

[Scott Huish]

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

Format in the date format you want.

 

[Joe4]

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

Format in the date format you want.

Scott,

That doesn't seem to work on his example of "Thu, Jan 04 2024".

Note that if you remove the "--" just to see what the formula part returns, you get:

Thu, Jan, 04 2024

which will not convert to a valid date by adding "--" in front of it.

 

[SOLTEC]

Joe, that gave me a long number and when I formatted the cell to a date gave me my short date. Thank you.

 

[Scott Huish]

=--SUBSTITUTE(MID(A1,6,11)," ",", ",2)

 

[Joe4]

Joe, that gave me a long number and when I formatted the cell to a date gave me my short date. Thank you.

Yep, that is exactly how Excel all dates (as long numbers), specifically the number of days since 1/0/1900.
You can see this by entering any valid date in Excel, and then changing the format of the cell to "General" or "Number".
Then you will see the date as Excel actually sees it.

So all dates really are in Excel are numbers with special date formatting applied to them.
That is why you can subtract two dates and it will return the number of days between them.

 

[Dave Patton]

You have a solution since your system has Regional Settings of mm-dd--yyyy.

Systems that have Regional Settings with International format i.e. dd-mm-yy may require the following.

Please confirm if E1 works correctly on your system.

T202401a.xlsm
	A			D	E
1	Thu, Jan 04 2024			04-Jan-2024	04-Jan-2024
2
3a

Cell Formulas
Range	Formula
D1	D1	=--CONCAT(INDEX(TEXTSPLIT(A1,{" ",","},,1),{3,2,4}))
E1	E1	=LET(t,TEXTSPLIT(A1,{" ",","},,1),DATE(INDEX(t,4),MONTH(INDEX(t,2)&"-1"),INDEX(t,3)))