Can you post an example of the data values?

Hi Steve,

No problem, here is one of the date values (copied directly from the original Excel doc):

5/22/2007 2:52:35 PM

I've also attached a screenshot of the error message I get. If you need any more information, please feel free to let me know.

Thanks,

Shane

I think you need to use the following format:
MM/dd/yyyy h:mm:ss a

Consult http://www.visokio.com/dates-and-times for more information

Hi Steve,

What is the difference between the format I used and the format you suggested? The only thing I can think of is that single digit month values need to have a leading zero, but when I tried that format I get the same exact error message. I should mention that I have an Omniscope file with a single date value (the value listed above) that I have been trying to convert for almost a week. First with the original data, then with a leading zero on month, then with a leading zero on hour, then with "PM", then with just "P", then with no "AM/PM" at all, then with every combination of these formats that I could think of. Again, in every case I got the same error message. Also, the dates-and-times page does not help, the "Cannot Parse" error is not mentioned at all and no solutions are suggested. It also refers to "AM/PM" as both symbol "a" and symbol "aa" so I am not sure which is correct. Please let me know what other options you can suggest.

Thanks,

Shane

In your screenshot you have "MMM d, yyyy h:mm:ss a" which would correspond to a date such as "Feb 2, 2010 9:53:27 PM".
So the time parts are correct, but the date parts you had wrong. Instead of "MMM d, yyyy" you want "MM/dd/yyyy".

I think "a" and "aa" are interchangeable and have no effect. While "MMM" means a short month name, "MM" and "M" mean month number and can be used interchangeably when *reading* dates; when *displaying* dates, they determine whether there is a leading zero.

Cannot parse simply means the format and the dates do not match up.

Note that ALL dates in your field must be in the same format for the conversion to work.

We recognise this is quite cryptic and hope to make it much more usable in future. The format codes are the ones supported in the Java programming language (so not particularly user-friendly, admittedly). For more information, see http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html

Hi Steve,

I appreciate your quick responses. I was under the assumption that the "custom date format field" in the error message/screenshot was asking me for a preferred output, not the format of the input. Once I changed that the conversion worked perfectly!

Thanks again!

Shane

In fact it's both. So when converting from text, you specify the input format, which is also used as the output format. You can then go back into the same dialog to change the display format or to drop the time portion, for example.

In DataManager, we've made this a bit clearer. In the Field Organiser block, you can specify both the input and output formats for date conversions. Do you have an Omniscope 2.6 DataManager license?

Hi Steve,

We are still using version 2.5 because it is the latest stable release. Once 2.6 is stable, we will start looking at DataManager.

Thanks,

Shane