Previous month based on current month

[spectraflame]

I am using the following formula to return the current month.

=TEXT(TODAY(),"MMMM")

However I would like to return the previous month. I know that if it were the year, I can just add -1 to the end of the formula, but that returns #VALUE when tried with the MMMM format.

Thanks,
Matthew

 

[Rick Rothstein]

Today() and Now() are volatile functions, better not use them.
The next function uses Date instead, and gives more flexibility, and saves a lot more keystrokes

Public Function PrevMonth() As String
Dim d As Date
d = DateSerial(Year(Date), Month(Date) - 1, 1)
PrevMonth = Format(d, "MMMM", vbMonday, vbFirstJan1)
End Function

The only possible down side to a VB solution is some companies do not allow its use. However, you make good points, so if VB is usable, a function like yours would be usable, then that is definitely a method to consider. I would consider simplifying your function a little bit though (along the lines of my formula posting)...

Public Function PrevMonth() As String
  PrevMonth = Format(Date - Day(Date), "MMMM")
End Function

Note that I also eliminated the optional arguments as I do not see how they would affect a function returning the name of the month prior to the current date's month.

 

[T. Valko]

Today() and Now() are volatile functions, better not use them.

Why?

The next function uses Date instead, and gives more flexibility, and saves a lot more keystrokes

Public Function PrevMonth() As String
Dim d As Date
d = DateSerial(Year(Date), Month(Date) - 1, 1)
PrevMonth = Format(d, "MMMM", vbMonday, vbFirstJan1)
End Function

A UDF for something so basic seems like massive overkill to me.

 

[Kreszch68]

From msdn:

Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.
The following Excel functions are volatile:

• NOW
• TODAY
• RAND
• OFFSET
• INDIRECT
• INFO (depending on its arguments)
• CELL (depending on its arguments)

 

[T. Valko]

From msdn:

Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.

The following Excel functions are volatile:

• NOW
• TODAY
• RAND
• OFFSET
• INDIRECT
• INFO (depending on its arguments)
• CELL (depending on its arguments)

I've highlighted the most important part of that paragraph.

Now we need to define "too much".

If you use 1000's of volatile functions you MIGHT notice a delay when some event causes a calculation. If you notice the delay then you are using too many volatile functions.

One time I was running some tests and had a file that contained 100 million RAND() functions (in Excel 2007). Yeah, there was a delay in calculation time but it wasn't that bad considering there were 100 million volatile functions calculating. IIRC, recalc took less than 1 min.

 

[Jonmo1]

There is definately a fine line between using too many volatile functions, and using too much VBA.

IMHO,
If it can be done without VBA, then it probably should be done without VBA.

But this is just a rule of thumb, rules are meant to be broken/bent.
It's up to each individual to decide when / why / how much to use Volatile functions and/or VBA.


And as Biff said, Volatile functions are only a problem when they are used ALOT..
And in this case, the function in question appears to be of the type that would only be used once, or perhaps only a few times.

But even if you wanted to use it thousands of times, a better solution would be to put =TODAY() in it's own cell, then refer to that cell in your formula thousands of times.

A1: =TODAY()
B1:B10000: =TEXT($A$1-DAY($A$1),"MMMM")

In this scenario, Today is only calculated once in A1
Then you have 10000 cells referring to A1.

 

[abinkar]

Another alternative...

=TEXT(TODAY()-DAY(TODAY()),"MMMM")

Please help, I need to add 1 day to the above formulas result value date.

 

[Aladin Akyurek]

Please help, I need to add 1 day to the above formulas result value date.

Not sure about your objective...

Is...

=TODAY()-DAY(TODAY())+1

what you are after?

 

[abinkar]

Thanks Aladin, thats what i wanted. Got it...!!!

 

[Aladin Akyurek]

Thanks Aladin, thats what i wanted. Got it...!!!

You are welcome. Thanks for providing feedback.

 

[Suavis]

Found the thread helpful.

Just thought I would add. In case you need to go more than one month back, try the following:


Results Based on today = 21 Oct 2015
2 Months Back: =TEXT(DATE(IF(MONTH(TODAY())-2>0,YEAR(TODAY()),YEAR(TODAY())-1),IF(MONTH(TODAY())-2>0,MONTH(TODAY())-2,MONTH(TODAY())+10),1),"MMMM YYYY") Result: August 2015
3 Months Back: =TEXT(DATE(IF(MONTH(TODAY())-3>0,YEAR(TODAY()),YEAR(TODAY())-1),IF(MONTH(TODAY())-3>0,MONTH(TODAY())-3,MONTH(TODAY())+9),1),"MMMM YYYY") Result July 2015
4 Months Bank: =TEXT(DATE(IF(MONTH(TODAY())-4>0,YEAR(TODAY()),YEAR(TODAY())-1),IF(MONTH(TODAY())-4>0,MONTH(TODAY())-4,MONTH(TODAY())+8),1),"MMMM YYYY") Result June 2015
10 Months Back: =TEXT(DATE(IF(MONTH(TODAY())-10>0,YEAR(TODAY()),YEAR(TODAY())-1),IF(MONTH(TODAY())-10>0,MONTH(TODAY())-10,MONTH(TODAY())+2),1),"MMMM YYYY") Result December 2014
11 Months Back: =TEXT(DATE(IF(MONTH(TODAY())-11>0,YEAR(TODAY()),YEAR(TODAY())-1),IF(MONTH(TODAY())-11>0,MONTH(TODAY())-11,MONTH(TODAY())+1),1),"MMMM YYYY") Result November 2014

etc, etc.