Search:

# Run Last month report on 5th

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

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:

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
It would always execute for the previous month.
hope this helps.

...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.