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 "/" ?

 

[keinz]

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), "/", "")

the substitute WORKED!