Calendar Script (This month a year ago)

[stuwannop]

Hi All

I've had a search through various threads but can't quite find what I want. I have a calendar script set up to return 1 or 0 for various date conditions but I'm missing a couple and I hope someone can help.

So far I have:

if(InYearToDate (Date, today(),0,4),1,0)									as Dates_FYTD_TY (Returns 1 for all dates in this financial year to date (starting in April)
if(InYearToDate (Date, today(),-1,4),1,0)									as Dates_FYTD_LY,(Returns 1 for all dates in previous financial year to date
if(InMonthToDate(Date, today(), -1),1,0)									All dates last month

I'm trying to now return a 1 or 0 for:

Any dates that are the previous year of the last month (in context of this that would be December 2013.

Thanks in advance

Stu

[rwunderlich]

I think it would be InMonthToDate uisng an offset of -13. You may have to adjust that number for your fiscal year.

=InMonthToDate(makeDate(2013,12,1),today(),-13)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#3]

Just an FYI - using InMonthToDate would give you only till today.

if(InMonthToDate(Date, today(), -1),1,0)     -      Dates till today of last Month (If today is 1/5/2015 then you would get 12/1/2014 to 12/5/2014)

[rwunderlich]

Good point. To get the full previous month, insert MonthEnd() like this:

=InMonthToDate(makeDate(2013,12,31),MonthEnd(today(1)),-13)

-Rob

[stuwannop]

Thank you both for your responses.

I couldn't quite get it to work using IF(InMonthToDate(makeDate(2013,12,31),MonthEnd(today(1)),-13),1,0) it just returned a 1 all the time - it's probably me not understanding the funtion correctly to be fair. (I also don't want to hard code December 2013 either sorry if I didn't make that clear)

I think the key is the 13 months though as I find the following solution seems to now work:

if(InMonthToDate(Date, today(), -13),1,0) 

[rwunderlich]

December 2013 was just an example. You should use your own Date field as you apparently figured out.

-Rob