Date of birth import from text files

[KGee]

I'm using code I found online to import data from batches of text files into my spreadsheet. Below are the headers from the pipe delimited file and the second to last field is date of birth in mm/dd/yy format. I need to calculate the patient's age at time of service from that field and I'm finding that records like the one below for patients born in the 1920's are showing up as 202x as opposed to 192x. I think it's Excel doing it and not the import. Any way to get around this?

SERVICE D/T|PATIENT #|RECORD #|UNIT|TYPE|DOB|LOC
2021-05-31 06:00:00|123456789|1234567|BA01|A|06/23/27|B

 

[Alex Blakenburg]

Yes Excel is doing it. Technically it's a window setting.
With a 2 digit year there is going to be some guesswork involved.
In Windows 10 Settings > Region > Additional date, time & regional settings > Region > Change date, time or number formats > Additional Settings (button) > Date (tab) > Calendar "When a two-digit year is entered, interpret it as a year between:"

Mine actually said 1950 and 2049 but was applying 1930 and 2029 (which is what it always used to be), however if you change it the new values are applied.

If you want to automate the conversion you would need to change this setting but you would have to decide on a rule:

• if your ages start at zero, you might want to set it to 1922 to 2021 but anyone over 100 years would be wrong

Note: Changing this setting will affect any application that relies on it to convert a 2 digit year, eg when you type a 2 digit year into excel in the future.

You could also import that field as text and use formulas to convert the two-digit year into 4 digits but you would still make a decision on the cut-off point, so if you chose 1922, again anyone over 100 would be wrong.
If you know you data population is only going to have people over say 10 years old you can reduce the cut-off point.

Here is a <9min video by Mike Girvin ExcelIsFun on this topic with some conversion options (minus the if statement you will need to work out when to convert up or down)
He gives 3 options, my preferred one would be:

 =SUBSTITUTE(B22,"/","/19",2)+0

The +0 at the end has the effect of converting the text result to a number (which you format as a date).

 

[KGee]

Very useful. Thanks for the guidance.

 

[Alex Blakenburg]

Thanks for the feedback. Glad I could help.