2029 rule when extract saved as CSV

[adambc]

I am working with a system that has a number of extract routines ...

When I run an extract the system displays the result on screen with dates in their dd/mm/ccyy format ...

But when I click the "Save as CSV" button the resulting CSV file has invoked the 2029 rule eg dd/mm/1929 becomes dd/mm/2029 ...

I know I can change my Windows settings for manual yy input but I can't find a way to do this for "imported" dates ...

Does anyone know how I can retain the dd/mm/ccyy WITHOUT invoking the 2029 rule when I save as csv?

Thanks ...

 

[Joe4]

Where does Excel come in?
Are you trying to do the "Save as CSV" from Excel?
Is there Excel VBA code behind the button?
If so, can you please post that VBA code?

 

[Micron]

I'm not following why 1929 becomes 2029. The rule is for 2 digit years but 1929 is not a 2 digit year? Your only option may be to use 2 part dates or run code or Find/Replace on the data.

 

[adambc]

Where does Excel come in?
Are you trying to do the "Save as CSV" from Excel?
Is there Excel VBA code behind the button?
If so, can you please post that VBA code?

@Joe4
@Micron

I've just taken another step forward on this ...

The issue is that the "code" behind "Save As CSV" is the system provider's code and not visible to me, so I can't at this point validate my conclusion!

The ON SCREEN output from the extract looks/feels/behaves like Excel but I think that might be a distraction?

What I think is happening is that the code behind "Save As CSV" has a parameter whereby only YY is passed to the CSV file and therefore the 2029 Rule gets applied when Excel is used to open/process the CSV file ...

The order of events ...

- the source system holds the date as dd/mm/ccyy eg dd/mm/1929
- when the extract is run the ON SCREEN displays dd/mm/ccyy eg dd/mm/1929
- when "Save As CSV" is clicked, the date in the resulting CSV file viewed in Notepad is dd/mm/yy eg dd/mm/29
- but when the CSV is viewed in Excel the date is dd/mm/2029 ie Excel has applied the 2029 Rule, which is the date that the PowerQueries I'm running against the CSV file is using

Which is why my conclusion is that the code behind Save As CSV is where I need to look (except I don't have access to that code so will have to rely on the system provider) ...

I'll update this post if/when I get to the bottom of what's happening ...

Thank you both for your help ...

 

[Micron]

If you can see the button you should be able to view the code - unless it is protected via password. If you know who wrote it, ask them to edit to not format the year as 2 digit, or ask for the password so you can modify.

Or apply a fix to the worksheet using your own vba if you can devise a reliable rule (e.g. minus 100 from every date in the csv)
Or reformat the csv to 4 digit year.
Or use date minus 100 in your PQ. I'm guessing there because I know nothing about PQ.

I agree with your notion that the problem lies within the button code.