9 Replies Latest reply: Jul 24, 2015 1:55 AM by Sasidhar Parupudi

# Month Name to Month Number

Hi guys,

I have 2 fields that I would like to compare to each other.

The one field is a period in the format YYYYMM and the other field is a date YYYY-MM-DD.

I would like to use an expression like

if(year(date)&month(date) = period, 1, 0)

but it spits out the month(date) as 'Jan' and not '01'...

how can I fix this?

Thank you

• ###### Re: Month Name to Month Number

Try

Num(Month(Date),'00')

• ###### Re: Month Name to Month Number

try,

if(date(DATE_Field,'YYYYMM')=period,1,0). it will be better to use this condition in script.

then on front end use this flag

• ###### Re: Month Name to Month Number

Let's supose that the field with the format YYYYMM is FLD1 and the second field (YYYY-MM-DD) is FLD2 then

If(Left(FLD1,4)*100+Right(FLD1,2) = Year(FLD2)*100+Month(FLD2) ,1 ,0)

Let me know

• ###### Re: Month Name to Month Number

if (Date(Date#(date,'YYYY-MM-DD'),'YYYYMM')=period,1,0)

• ###### Re: Month Name to Month Number

Thank you, this worked well

but now my next dilemma comes in when I try to do this calculation in a textbox... I have created a variable, that calculates the max period and only reflects the last month's values, except if you choose another period:

This is my list box equation....

=num(count({<PERIOD = {\$(vPeriod)}, Date(Date#(ACCCLOSEDT,'YYYY-MM-DD'),'YYYYMM') = {\$(vPeriod)} >} DISTINCT ACCNUM), '# ##0', '.', ' ')

and this is my variable equation:

=if(Left(GetFieldSelections(PERIOD),3) = 'NOT',
max({<PERIOD-={\$(=SubField(GetFieldSelections(PERIOD),'NOT ',2))}>} PERIOD),
max(PERIOD))

But it is not working... any thoughts?

• ###### Re: Month Name to Month Number

If your \$(vPeriod) returns a valid period then may be you can try the expression

in parts..

=num(count({<PERIOD = {"\$(vPeriod)"}} >} DISTINCT ACCNUM), '# ##0', '.', ' ')

I am doubtful if you can use like this in the set analysis , Date(Date#(ACCCLOSEDT,'YYYY-MM-DD'),'YYYYMM') = {\$(vPeriod)}

HTH

• ###### Re: Month Name to Month Number

regards

Marco

• ###### Re: Month Name to Month Number

I managed to sort it out, but moving the expression to the script..

I made a period of my date there instead of trying to do in the calculations...

thank you for all of your help!!!

• ###### Re: Month Name to Month Number

Hi Lucille,