Calendar Dates Script - Next Month

[stuwannop]

Hi Everyone

I currently have a line in my script that allows me to9 flag all dates last calendar month. This is:

IF(InMonthToDate(QualifiedDate, today(), -1),1,0) where qualifiedDate is of course my date value.

I want to be able to write a line that will flag all dates in the NEXT calendar month (bear in mind if it's December 2014 it needs to go to January 2015). Can someone help me out?

Thanks in advance

Stu

[#2]

Hi,

I'd do it this way:

-(addmonths(monthstart(QualifiedDate),-1)=monthstart(Today()))

Hope this helps

[MK_QSL]

Don't Exactly understood what you want...

If it is like you want to find last date for any date

add below in script...

MonthEnd(QualifiedDate) as MonthEndQualifiedDate

if your want last end date for next month

=MonthEnd(AddMonths(QualifiedDate,1)) as MonthEndQualifiedDateNextMonth

[stuwannop]

Hi Pierre

Thanks for the quick response, that seems to return a 0 if put in an IF statement.

I tried playing around with the operators (which I think it must be to do with) but I haven't quite cracked it. I ended up with

IF(-(addmonths(monthstart(QualifiedDate),1)=monthstart(Today())),1,0) but that returns last month so I'm still trying.

Manesh - I don't want to ultimately return a date as such, I want to return a 1 or 0 depending on whether the given date (Qualified Date) falls in the next calendar month or not.

Thanks

Stu

[#5 · Accepted solution]

It's a boolean formula, can be put in the script without an IF.

With an if, this should work:

if(addmonths(monthstart(QualifiedDate),-1)=monthstart(Today()),1,0)

[stuwannop]

Hi Pierre - Scratch my last response it actually works. User error I'm afraid - I've found a glitch in my data load. The script wasn't loading anything past todays date so of course it only returned a 0 flag.

Thanks for the answer and sorry to take up more of your time - appreciate your patience.

Stu