Start a new topic

Removing a timestamp and changing date format

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.


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

I added a dictionary. It solved my problem.

image

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

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 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

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,

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
Login or Signup to post a comment