excel power query - text to date error

[keinz]

Hi everyone,

I require your assistance...

The current data is shown in text format as: 1/26/23 (month, day, year)

I am having troubles trying to get excel to recognize this as a valid date - so I am using power query editor to create a new column but am getting an error

I tried splitting the columns but I guess its have issues recognizing it still - any insight on how to fix this error?

 

[keinz]

anything solution i tried doing is in excel itself..

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 = year(A2:A4)

 

[keinz]

made a little bit more progress..
more manual than i'd like but seems more straight forward

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

 

[Alex Blakenburg]

What is your system date default format?
Is it dd/mm/yyyy or mm/dd/yyyy ?

 

[keinz]

What is your system date default format?
Is it dd/mm/yyyy or mm/dd/yyyy ?

mm/dd/yyyy

 

[Alex Blakenburg]

The 2000 vs 1900 cut over point is a system setting, what do you get if you type 1/26/23 into a cell.

 

[keinz]

The 2000 vs 1900 cut over point is a system setting, what do you get if you type 1/26/23 into a cell.

it changes it to 1923 as the year and month and date is also wrong

the most accurate way i can get it so far is by using left, mid, right - to split the data into 3

but the issue im running into - is that it doesn't know how to ignore " / "

 

[Alex Blakenburg]

Are you using Windows 10 or 11 ?

Show me this screen in Windows settings

 

[Alex Blakenburg]

it changes it to 1923 as the year and month and date is also wrong

You seem to have 2 issues going on:
• 2 digits year of 2023 showing as 1923
• the year and mont and date is also wrong
Does the issue happen in all workbooks or only 1 specific workbook ?
If in the workbook with the issue you go to an empty cell and enter ctrl+semi-colon, what do you see?
Is it the same in other workbooks ?

To get to the control panel screen in my previous post in Windows 11:
• In the Windows Search box type "Control Panel"
• Select Region
• Additional Settings
• Click on the Date Tab at the top
• You should see the screen in my previous post, we are looking for the items
- Calendar > When a two-digit year is entered, interpret as a year between:
- Your Short Date default format