Skip to main content

I am importing three different reports into the same table.  One report’s DOB field provides the date as we wish it to appear, MM/DD/YYYY.  The other two reports provide a DOB format of DD-MMM-YY.  I am using a formula to convert the DD-MMM-YY format into MM/DD/YYYY and then using an automation to move the DOB Calculation into the DOB field.  
Some of the converted dates are future years.  The formula correctly converted 23-Sep-97 into 9/23/1997, but 29-Jan-56 became 1/29/2056.  How do I get the formula to create DOBs in the correct century?

Some years convert correctly, others not so much.
Current formula.

 

Hey ​@JillThorn,

Will years always correspond to 1900, and never to 1700, 1800, 2000 etc?

If so, we can easily get a formula working for you.

Also, are you regularly doing these imports, or is that a one-time import?

Mike, Consultant @ Automatic Nation

 

The DOBs can be in the 2000s, it could be a DOB for anyone currently alive.  

The 3 reports will be imported once a month.

 

Thank you,

Jill

An option is that you ran an IF condition,

 

if(

And(Datetime_parse({DOB}>{Today}, {DOB Import}),

DATEADD(Datetime_parse({DOB},-100,"Years"),

Datetime_parse({DOB})

 

So this will check if the DOB is in the future (greater than today), it will bring it back to the 1900. and the parse the date. 

Maybe you would need to add an extra condition in case you have someone born in 1920 and also someone born in 2020

 

Terms & ConditionsAccessibility statement