Data Cleaner for Dates in Tableau

A formula I’m working on to clean different types of dates in a calculated field.

I did this initially for Ancestry.com data, it’s not perfect, a work in progress.

date(
IF [Oxg Birth Date] = null then null

ELSE IF
len([Oxg Birth Date]) = 4
THEN
DATEPARSE(‘mm-dd-yyyy’,’01-01-‘+[Oxg Birth Date])
ELSE IF
len([Oxg Birth Date]) = 9
THEN
DATEPARSE(‘m-dd-yyyy’,[Oxg Birth Date])

ELSE IF
len([Oxg Birth Date]) = 10 and CONTAINS([Oxg Birth Date],’about’)=false
THEN
DATEPARSE(‘mm-dd-yyyy’,[Oxg Birth Date])
ELSE IF
CONTAINS([Oxg Birth Date],’about’) and len([Oxg Birth Date])=10
THEN
DATEPARSE(‘mm-dd-yyyy’,’01-01-‘+replace([Oxg Birth Date],’about-‘,”))

ELSE IF
CONTAINS([Oxg Birth Date],’before’) and len([Oxg Birth Date])=11
THEN
DATEPARSE(‘mm-dd-yyyy’,’01-01-‘+replace([Oxg Birth Date],’before-‘,”))
ELSE IF
len([Oxg Birth Date])=8
THEN
DATEPARSE(‘dd-MMMM-yyyy’,’01-‘+[Oxg Birth Date])
ELSE IF
( len([Oxg Birth Date])=9 and (CONTAINS([Oxg Birth Date],’July’)) or CONTAINS([Oxg Birth Date],’June’))
THEN
DATEPARSE(‘dd-MMMM-yyyy’,’01-‘+replace([Oxg Birth Date],’about-‘,”))

//March April
ELSE IF
( len([Oxg Birth Date])=10 and (CONTAINS([Oxg Birth Date],’March’)) or CONTAINS([Oxg Birth Date],’April’))
THEN
DATEPARSE(‘dd-MMMM-yyyy’,’01-‘+replace([Oxg Birth Date],’about-‘,”))

ELSE IF //August-1995
( len([Oxg Birth Date])=11 and (CONTAINS([Oxg Birth Date],’August’)) )
THEN
DATEPARSE(‘dd-MMMM-yyyy’,’01-‘+replace([Oxg Birth Date],’about-‘,”))
ELSE IF //October-1995 January-1995
( len([Oxg Birth Date])=12 and (CONTAINS([Oxg Birth Date],’October’)) or CONTAINS([Oxg Birth Date],’January’) )
THEN
DATEPARSE(‘dd-MMMM-yyyy’,’01-‘+replace([Oxg Birth Date],’about-‘,”))
ELSE
DATEPARSE(‘dd-MMMM-yyyy’,replace([Oxg Birth Date],’about-‘,”))
end end end end end
end end end end end end
)