Software

Inserting the current Monday's date in a Word document

Use this easy-to-implement Word macro to return the date for the current Monday, any day of the week.

It's common to date documents and reports, but things can get complicated if you create the document on a date other than the report date. For instance, you might compile a weekly report on Friday but use the previous Monday's date to identify it. The easiest solution is to just check the calendar and manually enter the right date. But what if you're creating a template for someone else to use? Or suppose you just want the template to handle the chore for you!

Entering dates in Word is easy-calculating dates is another matter. If all the conditions are just right, a nested field will work, but there are no guarantees; date fields just aren't flexible enough to always handle all possibilities. A macro is the best way to go. To enter the date for the current Monday, you can use the following macro:

Sub ReturnCurrentMonday()
 'Return Monday of current week.
 Dim dteMonday As Date
 dteMonday = IIf(Weekday(Date) = 1, Date - 6, _
 Date - (Weekday(Date) - 2))
 Selection.InsertBefore dteMonday
End Sub

To enter the macro, press [Alt]+[F11] to launch the Visual Basic Editor. From the Insert menu, choose module, and enter the above sub procedure. (You can add the module to the Normal template or the current document.)

To use the macro, return to Word and position the insertion point where you want the date (it doesn't have to be in a header or footer, but that's the most likely spot). Click the developer tab and then click Macros in the Code group. In Word 2003, choose Macro from the Tools menu, and then choose Macros. Select the macro and click Run (making sure you've selected the right option from the Macros In dropdown). That's all there is to it!

This macro relies heavily on the Weekday() function, which returns an integer for each day of the week:

  • Sunday = 1
  • Monday = 2
  • Tuesday = 3
  • Wednesday = 4
  • Thursday = 5
  • Friday = 6
  • Saturday = 7

If the current date is a Sunday, the true action subtracts 6 from the current date, returning the previous Monday. For every other day of the week, the false action kicks in, and it's a bit more complex. The Weekday(Date) - 2 component evaluates first. Subtracting this value from the current date returns the Monday for the current week. For instance, October 18, 2011 is a Tuesday, and the false action evaluates as follows:

10-18-2011 - (Weekday(Date) - 2)) =
10-18-2011 - (3 - 2) =
10-18-2011 - 1 =
10-17-2011

Returning the current week's Monday date is a frequent request, but you might need the macro to return another day of the week. You can use this basic expression to accommodate most any request by adding or subtracting the appropriate number of days. In addition, you can add the Format() function to force a specific date format.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
BraisbyI
BraisbyI

Hi, realy useful macro, but could realy do with this in excel, is it possible? Regards Ian

ppg
ppg

You can simplify the macro by using the optional parameter for Weekday which tells it what day to start the week on. I.e. the body of the macro could be dteMonday = Date - Weekday(Date, vbMonday) + 1 Selection.InsertAfter dteMonday In this case Weekday returns 1 for Monday, 2 for Tuesday etc.

ppg
ppg

At least in Excel 2007 a simple formula is =TODAY()-WEEKDAY(TODAY(),3) The 3 tells WEEKDAY to number the week as Monday=0,Tuesday=1...Sunday=6 If you need a macro Sub ReturnCurrentMonday() 'Return Monday of current week. Dim dteMonday As Date dteMonday = Date - Weekday(Date, vbMonday) + 1 Selection.Value = dteMonday End Sub will put Monday's date in the currently selected cell

winlak
winlak

You could just use her formula in the cell you want the Monday to appear. Replace date with TODAY() IF(WEEKDAY(TODAY())=1,TODAY()-6,TODAY()-(WEEKDAY(TODAY())-2))

BraisbyI
BraisbyI

Many thanks ppg, I had worked out most of this except the "vbMonday", I currently use the TODAY() function but my workbook has a different sheet for each day of the week with tuesday relying on mondays date, which works fine until you have a bank holiday monday and start the week on the tuesday. This should sort the problem though Many thanks for the help and also thanks to winlak and ssharkins Regards Ian

ssharkins
ssharkins

As winlak suggests, just enter the formula into a cell -- should work just fine. Let me know if you have any trouble.

ssharkins
ssharkins

vbMonday is a VB constant, so it won't work in a formula (I think) -- only VBA code.

Editor's Picks