Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Find Date of Monday previous to Today

  1. #1
    Join Date
    9th January 2006
    Location
    Lincolnshire, UK
    Posts
    24

    Find Date of Monday previous to Today

    We have an excel report which looks at active orders and arranges them as a timeline according to the date in which they are due to be completed. The timeline shows all jobs due to be completed in a "week commencing" which SHOULD always begin on a Monday.

    I am able to show weeks from today by inserting '=today()' in H5, then by using '=h5+7' to get following week commencing date.

    The problem with this is that i have to run the report on Monday for all the Week commencing dates to be mondays.

    How can i find the date of the Monday previous to today?

    Hope that makes sense!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,305

    Re: Find Date of Monday previous to Today

    Hi,

    Try,

    =H5-WEEKDAY(H5,2)+1

    HTH

  3. #3
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,006

    Re: Find Date of Monday previous to Today

    Hi Liz

    consider

    =TODAY()-WEEKDAY(TODAY(),2)+1

    Wigi

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th January 2006
    Location
    Lincolnshire, UK
    Posts
    24

    Re: Find Date of Monday previous to Today

    Thankyou very much - both versions work!

    Don't suppose anyone could explain the logic of the string just so that i understand better for another time?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,006

    Re: Find Date of Monday previous to Today

    Risking that Kris is typing at the same moment as I am now, still an answer:

    =TODAY()-WEEKDAY(TODAY(),2)

    gives the date of last Sunday. It is the day of today, minus 1 if we are on a Monday, 2 if on a Tuesday, ..., till 7 for a Sunday. The 2 as the last argument refers to whether weeks start on a Monday or a Sunday.

    Knowing the date of Sunday, we do +1 and have the date for Monday.

    OK now?

    Wigi

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th January 2006
    Location
    Lincolnshire, UK
    Posts
    24

    Re: Find Date of Monday previous to Today

    Thankyou very much, it's a bit like being back at school - it's nice to be given the answer but I'll only learn if I understand the reasoning!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,006

    Re: Find Date of Monday previous to Today

    That's the correct way to do it indeed.

    Good luck with your Excel work.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: April 4th, 2008, 10:15
  2. Checking date in cell A1 Monday or not
    By priya_vba in forum EXCEL HELP
    Replies: 10
    Last Post: April 11th, 2007, 18:58
  3. Test If Date Equals A Monday
    By Ish56 in forum EXCEL HELP
    Replies: 6
    Last Post: September 20th, 2006, 10:41
  4. Identifying a Monday in a date range
    By SUJU in forum EXCEL HELP
    Replies: 7
    Last Post: March 9th, 2005, 18:42
  5. if today is Monday(weekday)
    By adncmm1980 in forum EXCEL HELP
    Replies: 3
    Last Post: December 14th, 2004, 18:12

Bookmarks

Posting Permissions

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