Create End of Month date for each month of current year

[#1]

Hi People,

How do I generate a variable for End of Month for Current Year and each month. Something like this:

Date('Jan' & 'vCurrentYear') so I want Jan through Dec. I think MMM-YYYY will return the End of Month won't it?

Please assist me if you can.

Thank you,

OnePuttGirl

[MayilVahanan]

Hi

MonthEnd(DateField) gives the end date

ex:

MonthEnd(today()) gives 10/31/2012

For MMM-YYYY format:

=Date(MonthEnd(Today()),'MMM-YYYY')

Hope that helps

[swuehl]

Yes, monthend() is probably part of the solution. Do you want to create a field with 12 values, one for each month in current year?

Maybe like

Let vCurrentYear = 2012;

LOAD

Date(floor(monthend(makedate($(vCurrentYear),recno() )))) as MonthEnd

autogenerate 12;

Please note that monthend will return a timestamp for the last millisecond for that day, so if you want to compare that value to a Date numerically, you need to use floor / daystart.

You can also do it like:

Let vCurrentYear = 2012;

LOAD

addmonths(makedate($(vCurrentYear),1,31),recno()-1,1) as MonthEnd

autogenerate 12;