Dynamic month naming

[#1]

Hi All,

I’ve got a sales data with headers (expressions);

M0 – M1 – M2 – M3 – M4 – M6 – M7 …

M0 is current month, M1 is last month, M2 is the month before, etc..

How can I turn these into month names? Basically I’m askingfor dynamic month naming.

I've tried M0 = month(today()) which works then tried M1 = month(today()-1 which didn't work.. any idea??

Thanks,

Mem

[Anonymous]

You could try something like this in your script:

   (Month(Today())- (right(Monthfield,1))) as Month,

This will return number of the right month.

But you have to modify it to your own script, but maybe you can start from here.

And keep in mind that you could better solve it on a MonthYear field in stead a MonthField.

[#3]

Hi Dennis,

Unfortunately that didn't work. But I've managed to get a number output by writing;

=Month(today())-Month(30) This gives a result of 6 (Jun)

=Month(today())-Month(60) This gives a result of 5 (May)

=Month(today())-Month(90) This gives a result of 4 (Apr)

and so on ...

If I could turn these numbers into month names that would solve my problem I guess..

Cheers,

Mem

[swuehl]

Hi,

try

=month(makedate(2011,month(today())-1,1))

for M1

=month(makedate(2011,month(today())-2,1))

etc.

P.S:

I would not use "month(today()) - month(30)" to get the previous month, but month(today())-1

Both result in 6 (as of today), buth month(30) means month('1900-01-29') which is Jan (or 1), but I think this is misleading and depending on start of internal calendar.

In above expression, I make a date (because month() expects a date, using calculated month number and arbitrary year and day number (i.e. 2011, 1)

[SunilChauhan · Accepted solution]

month(addmonths(today(),-1))

for Month in text like jan feb

num(month(addmonths(today(),-1))) for 1,2,3 ,4

thanks

Sunil Chauhan

[#6]

Thanks both,

They both do the job except Swuehl's method stops working for "-7" (Dec-2010) since the data is for 18 months.

Sunil's works perfectly.

Cheers,

Mem