Discussion on:

Inserting the current Monday's date in a Word document

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.

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

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

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

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

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