Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002

    Unanswered: find last Monday's date

    How do I get last Monday's day using today's date?

    'if today is Thursday this gives last Thursday's date.
    Me![Beginning Entry Date] = DateAdd("ww", -1, todate)

    I need Monday's date of previous week. I tried datepart and datediff function but what they give me are integer values.

    Can someone help with this?

    Thank you!

  2. #2
    Join Date
    Jul 2003
    This should work:
    DATEADD("d", -((datepart("w",now)-2)+7), now)

    The 2 in the formula indicates that Monday is the 2nd day of the week.
    You'll have to adjust if you have a day other than Sunday as the first day of the week.

    You could also create a function like this:

    Function LastMonday() As Date
    Dim bToday As Byte, intDayDif As Integer
    bToday = DatePart("w", Now)
    intDayDif = (bToday - vbMonday) + 7
    LastMonday = DateAdd("d", -(intDayDif), Now)
    End Function

  3. #3
    Join Date
    Jan 2003
    Aberdeen, Scotland, UK


    Darm it. RedNeckGeek beat me to it. His answer's better anyway but since I just spend ten mins working this out I'm going to post it any way.

    Dim IntDay As Integer
    Dim IntCounter As Integer

    'If today is a Monday then start yesterday
    'so it doesn't pick up today as a Monday
    If Weekday(Now(), vbMonday) Then
    IntCounter = 0
    IntCounter = 1
    End If

    'Loop till it's a Monday
    Do Until IntDay = 2
    IntCounter = IntCounter - 1
    IntDay = Weekday(DateAdd("d", IntCounter, Now()))

    'Last monday was intcounter days ago.
    MsgBox "The date last monday was: " & DateAdd("d", IntCounter, Now())

    Oh well.

  4. #4
    Join Date
    May 2002

    find last monday's date

    Thank you very much for your help.
    I have better understanding in using these functions and also an alternative way to write them.

    Thanks again!

  5. #5
    Join Date
    May 2006

    Find Last Monday of the month

    Hi guys! Thanks for your post (I know it was a LONG time ago). I was trying to find a way to find the last Monday in May, and so I took what you posted and modified it a little. I had to tweak it slightly because for the last Monday in May of 2009 it was returning June 1st instead. So, here's what I used to get the last Monday in May (validated for 2000-2011):

    DateAdd("d", -((DatePart("w",dtmLastDay-1)-1)), dtmLastDay)

    Note: dtmLastDay is a variable I used to store the last day of the month that I passed in to the function (which happened to be May in my case).

  6. #6
    Join Date
    Feb 2004
    One Flump in One Place
    Lol - that was almost 3 years Glad you finally cracked it. Welcome (back) to the forum.
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2009
    Google, KS
    Search function ftw. This helped me tremendously, as RedNeckGeek already knows I have trouble wrapping my head around date functions.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts