Monarch v2021.1 - Data Prep Studio multiple date fields with 1/1/1900
I am fairly new to Monarch and have spent most of my time using the data prep studio to clean some data which contains several date fields. Within these date fields there are instances of 1/1/1900 that I would to remove but there isn't a replace option available to complete this. Is there another option that I can use before I export the data out? Thanks in advance!
Find more posts tagged with
Hi Todd,
Two options came to my mind:
First option:
1. Convert -> Date/Time to Text
2. Replace ("1/1/1900" -> "")
3. Convert -> Text to Date/Time
Second option:
Create a new formula column for each original date field like if ([original date field] = ctod("1/1/1900"); ctod(""); [original date field])
-Lasse
Hey @Chris Porthouse - They are fields that I would like to see the date and merely want to remove what I am categorizing as a bad date in 1/1/1900. I ended up using the suggestion from @Lasse and it did accomplish what I was trying to do.
Thank you both for replying to my question. I really appreciate it.
~Todd
Normally you see that when you have a date/time field and you are missing the date portion. It will default to 1/1/1900. If you don't need the date portion, you can format the field to just show the time.
If the field is truly a date field and Monarch cannot convert to a valid date, it will usually show up as a null value. Are you able to share an image or example of your data to pin point the exact issue?