Clean, swift and deadly: #1 - Last Monday

Ever since I began coding I have always been a fan of concise code that does exactly what you want with minimal effort, both for the computer and for the coder who is tasked with maintaining or otherwise using the code. I liken it to an art form.

Suppose you have an Excel workbook which you use for a timesheet, with a date cell indicating the Monday of the current week, like so:

If used as a template, every time that the user would make a new timesheet, he/she would have to complete the start date on the new copy. I know from experience that, when you have been rushed off your feet the whole working week, amending the start date can be difficult to remember.

The week start is surprisingly easy to automate, though. A very simple function would be as follows:

Function LastMonday(Optional ByVal FromDate As Date) As Date
    
    If FromDate = 0 Then FromDate = Now
    LastMonday = FormatDateTime(FromDate - Weekday(FromDate, vbMonday) + 1, vbShortDate)
    
End Function

What the above does is, given any date, will return the Monday immediately before the given date. The function Weekday(FromDate, vbMonday) will return between 1 and 7 depending upon how many days after Monday the current day is. The function as a whole can also be called without arguments, and assumes the current date to work from.

In the Workbook you can then have something along the lines of:

Private Sub Workbook_Open()

    Dim StartDate As Range

    Set StartDate = Sheet1.Cells(2, 1)

    If IsEmpty(StartDate.Value) Then Sheet1.Cells(4, 3).Value = LastMonday

End Sub

Voila! If you clear the starting date cell in the template and save, the starting date will automatically be set to last Monday so long as events are enabled. Now all you have to do is convince your co-workers that you didn't put in malicious code in the workbook!