I have a data file that provides this date and time stamp in the following manner: May 24 2016 - 10:08pm

And I need to format it for 2 different profiles due to different user settings: mm/dd/yyyy with the time stamp removed AND dd/mm/yyyy

If anyone can offer assistance, that would be great.

Hi Claire,

Often times Excel will display dates/times as May 24 2016 - 10:08pm in Excel, but it actually is in a format like 05/24/2016 22:08 when you click in the cell. Can you tell me what the actual format is within the data file?

Thanks,

John

Hello John,

When I click on the cell nothing changes, the contents of the cell still present as May 24 2016 - 10:08pm. Also when checking the Data-Sort, the options are A->Z and Z->A, not a date.

Hi Claire,

You may need to speak with whoever you are getting the data from and see if there is any way they can at least give you a date in a numeric format. RegEx can be a powerful tool, but it's not capable of taking a text based date and converting it to a numeric format. The only way to make this happen within ImportOmatic would be through custom code in the API

Thanks,

John

I'm having a similar problem, though there is no timestamp in the original file. I continue to get an "Invalid Fuzzy Date" error for a birth date field. I checked the cell value and the formatting and there is no timestamp. I tried deleting the column and entering the value manually (rather than rely on the source file). Any suggestions? I don't see a timestamp anywhere in the cell. Thanks.

HI Frank,

If your data appears to be in MM/DD/YYYY format Excel is likely adding a timestamp in the background that you can't see. Saving the file as a CSV typically resolves this.

Thanks,

John

Thanks John. This worked. Excel does some strange things...