Date for Previous Monday Excel

Question

Today is 02/27/2013 which is Wensday. I need formula which will return me date for previous Monday.which would be (02/17/2013)

I need to so I can use for file name or email subject in my vba code which sends emails.

With oMail
     'Uncomment the line below to hard code a recipient
     .To = "[email protected]"
     'Uncomment the line below to hard code a subject
     .Subject = "Current Report"
     .Attachments.Add WB.FullName
    .Display
End With

Answer 1

Public Function LastMonday(pdat As Date) As Date
    LastMonday = DateAdd("ww", -1, pdat - (Weekday(pdat, vbMonday) - 1))
End Function

Weekday(yourdate, vbMonday) returns a 1 for Monday, 2 for Tuesday, etc. so

pdat - (Weekday(pdat, vbMonday) - 1)

Will give us the most recent Monday by subtracting the Weekday()-1 # of days from the passed date.

DateAdd("ww", -1, ...)

subtracts one week from that date.

LastMonday(cdate("2/27/13"))

Returns 2/18/2013 (which is Monday, not the 17th)