2 Replies Latest reply on Sep 4, 2014 11:09 AM by slraymond

# Converting Date from dd-mmm-yyyy

I was using this calculation to convert a date from dd-mm-yyyy:

Let([

adj =Substitute ( Self ; "-" ; ¶ ) ;

month = GetValue ( adj ; 2 ) ;

day = GetValue ( adj ; 1 ) ;

year = GetValue ( adj ; 3 )

];

Date ( month ; day ; year )

)

All was well, until the format of the spreadsheet from which I import data changed. Now it's dd-mmm-yyyy, as in "08-jun-2014."

Now, my dates all are converted to something between 12/1/2013 and 12/31/2013. What is happening? I assume it has to do with the text month.

• ###### 1. Re: Converting Date from dd-mmm-yyyy

slraymond wrote:

What is happening? I assume it has to do with the text month.

That's a good guess …

Try:

Let ( [

adj = Substitute ( Self ; "-" ; ¶ ) ;

monthName = GetValue ( adj ; 2 )  ;

~day = GetValue ( adj ; 1 ) ;

~year = GetValue ( adj ; 3 ) ;

monthList = "jan¶feb¶mar¶apr¶may¶jun¶jul¶aug¶sep¶oct¶nov¶dec" ;

~month = ValueCount ( Left ( monthList ; Position ( monthList ; monthName & ¶ ; 1 ; 1 ) ) )

] ;

Date ( ~month ; ~day ; ~year )

)

• ###### 2. Re: Converting Date from dd-mmm-yyyy

Works great, thanks! I'm going to have to take a few minutes to study this; I'm a bit handicapped with calculations.