Define variables

[#1]

Hi I need to define dynamic variable based on today's date. need assistance when it comes to year & month logic.

gives me current day.

Let vCurrentDay = Day(today());

gives me current month in words & in numbers.

LET vCurrentMonth

= Month(Today());

Gives me current year:

Let vCurrentYear = Year(Today());

I need help defining following.

1. Give me Month start variable that will be 3 months prior to current month regardless of what date we fall into current month.

          So if we are in Mar it should return Jan. Basically it is going back entire 3 months not 3 months from today.

1. Give me Year  variable that will fall into whatever MonthStart (point#1) falls into.

thanks

[maxgro · Accepted solution]

AddMonths(MonthStart(Today()),-2)

Year(AddMonths(MonthStart(Today()),-2))

[#3]

Can you please explain how formula works.

[maxgro]

monthstart(Today()) is the start month of today 

addmonths(........, -2) is the start month of today minus 2 months

my date is in DD/MM/YYYY format

[manojkulkarni]

MonthStart ()

Returns a value corresponding to a timestamp with the first millisecond of the first date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

monthstart ( '2001-10-19' ) returns '2001-10-01' with an underlying numeric value corresponding to '2001-10-01 00:00:00.000'

monthstart ( '2001-10-19', -1 ) returns '2001-09-01' with an underlying numeric value corresponding to '2001-09-01 00:00:00.000'

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'