Month number calculation for to include past months

[bcooke]

Hello all,

Currently working on trying to get a formula to calculate the current month but -2. i.e. want it to populate if 10 to output 8. Although when January or February comes around current month of 1 or 2 will be incorrect as it will output -1 and 0. Is there a way I can have if it say January to output 11 for November and February output 12?

Current Formula being used below: 

DateTimeMonth(DateTimeToday())-2)

Again the above formula would not calculate correctly for January and February.

[BrandonB · Accepted solution]

DateTimeFormat(DateTimeAdd(DateTimeFirstOfMonth(),-2,"Months"),"%m")

This will do the trick for you. The first formula shows you that you can do a datetimeadd which will subtract two months from the first of the current month. The second formula shows you the exact same formula as the one above, except it is using datetimeformat to pull out the month number. You only need the second formula for your purpose but I wanted to show both so it makes sense what is happening. 

 

[bcooke]

Hello BrandonB!

Appreciate the help. It worked perfectly and even added the 0 that I would of needed also.

Thanks again,

-bcooke

[BrandonB]

Of course! If you ever run into a situation where you need leading zeroes, another handy function is PadLeft(String, len, char)

If you have single digits for some and double digits for others and need everything to be two characters with a zero added to the front if not, it would look like this:

PadLeft([String with the digits], 2, "0")

This will add a zero to the front of the values until they are two characters long. You can adapt this to other situations by changing 2 to whatever length you need and the "0" to whatever character you need.

[Ye_Olde_Baller]

I am trying to do the following:

if paid date month is less than or equal to incurred date month +3 months = y else no,

Dates are in YYYY-MM-DD format.

[BrandonB]

@Ye_Olde_Baller it should be something like this, assuming that your paid date month and incurred date month are both date data types. If not, you will want to use a date time parse tool prior to this formula to get them into a date data type. 

IF [Paid Date Month] <= DateTimeAdd([Incurred Date Month], 3, "months")

THEN "y"

ELSE "no"

ENDIF