mid formula to only include numbers?

[keinz]

the format 1/26/23 - i split it up into 3 different columns to represent each number with formulas (left, mid, right) then using the year formula to combine the 3
but the issue im getting is that since the 'year' is in 2 digits (23) in this case, its showing as 1923 rather than 2023 - how could I get around this?

so what i am doing is:

A1 = 1/26/23
A2 = left(A1,1)
A3 = mid(A1,3,2)
A4 = right (A1,2)
A5 = date(A2:A4)

A4 formula is showing the last 2 digits of the cell with the test 1/26/23 - which is 23
I just did =2000+23
this will help me get the proper year then I can use the DATE function to get the proper date

However - one thing I noticed is that for my "Day" cell - if its at the beginning of the month it will include the "/" which messes up the whole formula...

so for example
1/26/23

mid(a1,3,2) is showing 26 - perfect

but if the dates is 2/1/23
mid(a1,3,2) shows 1/

Is there anyway to have the mid formula remove the "/" ?

 

[footoo]

If your date format default is m/d/y then try Datevalue : =DATEVALUE(A1) and format the result as m/d/yyyy (or whatever).

 

[keinz]

If your date format default is m/d/y then try Datevalue : =DATEVALUE(A1) and format the result as m/d/yyyy (or whatever).

the problem with the initial data for 1/26/23 - excel is not recognizing it as a date -and in some cases, when it does... it thinks the "23" for year is 1923 rather than 2023 - this is crucial because i also use formula: =text(A1,"DDDD") - but if the year is wrong then the day of the week will also be wrong

so i had to break it up then re-do it for it to recognize it

not sure why its like that

 

[footoo]

What is your default date format?

 

[keinz]

What is your default date format?

mm-dd-yyyy

i've played with the date format - it doesn't work as i'd like

the only thing closest to what i need right now is doing this:

"
However - one thing I noticed is that for my "Day" cell - if its at the beginning of the month it will include the "/" which messes up the whole formula...

so for example
1/26/23

mid(a1,3,2) is showing 26 - perfect

but if the dates is 2/1/23
mid(a1,3,2) shows 1/ "

Is there anyway to have the mid formula remove the "/" ?

 

[footoo]

Have you tried DATEVALUE ?

 

[keinz]

Have you tried DATEVALUE ?

yes - sadly it didnt work

the closest to what i want so far is doing left, mid ,right - but need a way to remove " / "

 

[footoo]

Can't think why DATEVALUE doesn't work. What does it return?

The way you are using is very klunky.
Try =SUBSTITUTE(MID(A1,3,2), "/", "")

 

[footoo]

Also, what about when the month is 2 digits ?

 

[Dave Patton]

with your approach

T202301a.xlsm
	A
1	1/26/23
2	1
3	26
4	23
5	26-Jan-2023
6
1f

Cell Formulas
Range	Formula
A2	A2	=LEFT(A1,1)
A3	A3	= MID(A1,3,2)
A4	A4	=RIGHT(A1,2)
A5	A5	=DATE("20"&A4,A2,A3)

Try Data Text to Columns date mdy