Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Run Last month report on 5th

  Asked By: Lewis    Date: Sep 03    Category: MS Office    Views: 570
  

On 5th of every month, using scheduled task, I like to run last month report.
But don't know how to address first day of last month and months ending on 29 or
31. This is what I am doing,
ldtStart = Format(Now - 35, "MM/DD/YYYY")      
ldtEnd = Format(Now - 5, "MM/DD/YYYY")
 
thanks

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Noah Evans     Answered On: Sep 03

Here's how I would handle it:
Sub DateCalc()
Dim LastMon, LastDay, CurYear
Dim CurMon, ldtStart, ldtEnd
CurYear = Year(Now)
LastMon = Month(Now) - 1
Select Case LastMon
Case 2
If (Month(DateSerial(CurYear, 2, 29)) = 2) Then
LastDay = 29
Else
LastDay = 28
End If
Case 4, 6, 9, 11
LastDay = 30
Case Else
LastDay = 31
End Select
ldtStart = Format(DateSerial(CurYear, LastMon, 1), "MM/DD/YYYY")
ldtEnd = Format(DateSerial(CurYear, LastMon, LastDay), "MM/DD/YYYY")
MsgBox ldtStart & Chr(13) & ldtEnd
End Sub
this should work no matter WHAT day  of the month  you decided to run  the
scheduled task.
It would always execute for the previous month.
hope this helps.

 
Answer #2    Answered By: Candace Foster     Answered On: Sep 03

...and to simplify formula...

Sub DateCalc()
Dim LastMon, LastDay, CurYear
Dim ldtStart, ldtEnd
CurYear = Year(Now)
LastMon = Month(Now) - 1
ldtStart = Format(DateSerial(CurYear, LastMon, 1), "MM/DD/YYYY")
ldtEnd = Format(DateSerial(CurYear, LastMon + 1, 1) - 1, "MM/DD/YYYY")
MsgBox ldtStart & Chr(13) & ldtEnd
End Sub

OR...

Sub DateCalc()
Dim ldtStart, ldtEnd
ldtStart = Format(DateSerial(Year(Now), Month(Now) - 1, 1), "MM/DD/YYYY")
ldtEnd = Format(DateSerial(Year(Now), Month(Now), 1) - 1, "MM/DD/YYYY")
MsgBox ldtStart & Chr(13) & ldtEnd
End Sub

 
Didn't find what you were looking for? Find more on Run Last month report on 5th Or get search suggestion and latest updates.




Tagged: