• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2221
  • Last Modified:

Access VBA - Have the Previous Month display on an Excel report

I need the current month - 1 to display in Excel using VBA.

I tried this in my template
Month(today())-1 and it gives what looks like a month (ie...for current it's 7) but when I change the format to mmmm then it displays January.  Basically it looks like it's having the month be the day.  Anyway, can someone help me please please please?  I don't care if it's in the excel template or in my vba code.  Either one will do.   Thanks - you guys rock.

Julia
0
artgal
Asked:
artgal
  • 2
  • 2
1 Solution
 
fulscherCommented:
Month(today())-1 returns a number and not a date. If you format this number as mmmm, Excel assumes that it's January 7, 1980 and therefore displays "January".

Here's an Excel formula that will do it:

=DATE(IF(MONTH(NOW())=1,YEAR(NOW())-1,YEAR(NOW())),IF(MONTH(NOW())=1,12,MONTH(NOW())-1),1)

And here's a bit of VBA code to do it:

Public Function LastMonth() As Date
    Dim D As Date
    D = Now
   
    LastMonth = DateSerial(IIf(Month(D) = 1, Year(D) - 1, Year(D)), IIf(Month(D) = 1, 12, Month(D) - 1), 1)
End Function

Hope this helps
Jan
0
 
LucasMS Dynamics DeveloperCommented:
You can try the format function combined with DateAdd:

Format(DateAdd("m", -1, Date), "mmmm")

That will give you the month - 1

Good luck
0
 
fulscherCommented:
lucas911

It's way more elegant than my code, but... what would the function do if you try this on March 31st? (just wondering...)

Jan
0
 
naivadCommented:
It works because it is subtracting a month, not a day...

Format(DateAdd("m", -1, #3/31/2003#), "d") = February 28th

If the day is greater than the last day of the month, it just displays the last day of the month.
0
 
naivadCommented:
I think you should have given lucas the points on this, I was just explaining the answer.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now