Date of birth import from text files

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
537
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:
Excel Formula:
 =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).

 
Upvote 0
Solution
Very useful. Thanks for the guidance.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top