# Discussion on: Inserting the current Monday's date in a Word document

7
Comments

### Join the conversation!

Follow via:
RSS
Email Alert
View:
Show:
0 Votes
###### Start the week on Monday
ppg 25th Oct 2011
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.
0 Votes
###### Excel version
BraisbyI 25th Oct 2011
Hi, realy useful macro, but could realy do with this in excel, is it possible?
Regards
Ian
0 Votes
###### excel Version
winlak 25th Oct 2011
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))
0 Votes
Contributr
###### Thank you!
ssharkins@... 25th Oct 2011
As winlak suggests, just enter the formula into a cell -- should work just fine. Let me know if you have any trouble.
0 Votes
###### excel version
ppg 26th Oct 2011
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
0 Votes
###### excel version
BraisbyI 26th Oct 2011
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
0 Votes
Contributr
###### vbMonday
ssharkins@... 26th Oct 2011
vbMonday is a VB constant, so it won't work in a formula (I think) -- only VBA code.
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.