Date conversion

[timsmith]

Hi, I've been looking through the forums but haven't quite figured out my date conversion problem.

I've gotten the date into this format:

3 Feb. 2008

and have tried this formula to get it into a date format:

DateTimeFormat(DateTimeParse([Date2],'%d/%b/%Y'),'%m-%d-%Y')

but I get null values where the day is a single digit, e.g.: 3 Feb. 2008

Specifically, I get a message that the day of month value is out of range 1..31.  Somehow, when the day is a single digit, the month value isn't seen properly.

Thank you for any help.

[MarqueeCrew]

@timsmith,

If your target variable is a DATE field (a valid date), then this formula is what you're looking for:

DateTimeParse([Date2],'%d %b. %Y')

As you can see, it takes your input 3 Feb. 2008 and reformats it to:  2008-02-03

It expects to see a DAY# followed by a SPACE followed by a MONTH ABBREVIATION followed by a PERIOD followed by a SPACE followed by a YEAR.

Hopefully this solves your challenge.

Cheers,

Mark

[timsmith]

Mark,

  Thanks very much for your reply.

  I tried that but am still getting errors for the single-digit days.  Here's what I see for Jan. 1, 2017, for example:

  I has no trouble with any days of the month greater than 9; it's only the single digit days that I see errors for.  

  -Tim

[MarqueeCrew]

3 Feb. 2008 and

Jan. 1, 2017

require different formulas.

The formula that I provided solves for the first case.  While I can solve for the second case, is your input a mixture of different formats?  If it is, then you'll need to check the format for any given field before converting it.

DateTimeParse([Date2],'%b. %d, %Y')

Does this make sense?

Mark

[timsmith]

I understand -- and I wasn't clear about the data I am working with.

All the data look like this, which shows what you see in the Date2 field on the far right:

The formula works fine for all but the first two rows, the ones with the single digit day numbers, Feb. 4 and Jan. 7.  I've tried taking out leading and trailing whitespace but that didn't solve the problem.

Thanks again Mark for help me with this. 

Tim

[MarqueeCrew]

can you save the date2 data to an EXCEL :( file and post it.  I'll reply with a solution promptly.

Thanks,

Mark

[timsmith]

Sure.  Here you go. 

Thanks very much, Mark!

[MarqueeCrew]

Spoiler

DateTimeParse([Date2],'%d %b. %Y')

That works 100% of the time with the file that you gave me.

If you have whitespace, try:

DateTimeParse(Trim([Date2]),'%d %b. %Y')

I can watch you via WebEx now and control your screen to get you a 100% solution working.  For me, it looks fine with your data.  If you want a silent webex, please PM me with your email & I'll set it up.

Cheers,

Mark

[MarqueeCrew · Accepted solution]

@timsmith,

Magic!  Not the good kind.  I can't explain why it is the way that it is, but if the input to the formula is saved and then read into the formula, it works.  My solution required me to REFORMAT the date using a regular expression and then padleft the date if it was a single digit.  Then the parse function worked perfectly.

@BenG, if you have time to work with me and Tim, please contact me (not today) and I'd like to explore this with you.  Literally, the adobe inputs were replaced with text inputs (sourced by browsing their output, cutting and pasting them to another workflow) and my original solution worked.

This was all conducted with version 10.6

Cheers,

Mark

[timsmith]

Can't thank you enough, Mark! I appreciate your persistence in figuring this out.

Tim