Monarch v2021.1 - Data Prep Studio multiple date fields with 1/1/1900

Todd Klemp
Todd Klemp Altair Community Member
edited October 2022 in Community Q&A

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!

 

Tagged:

Answers

  • CPorthouse
    CPorthouse
    Altair Employee
    edited September 2022

    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?

  • Lasse
    Lasse Altair Community Member
    edited October 2022

    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

  • Todd Klemp
    Todd Klemp Altair Community Member
    edited October 2022

    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

Categories