Announcement

Collapse
No announcement yet.

Find Date of Monday previous to Today

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    Re: Find Date of Monday previous to Today

    Hi,

    Try,

    =H5-WEEKDAY(H5,2)+1

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Find Date of Monday previous to Today

      Hi Liz

      consider

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

      Wigi
      Regards,

      Wigi

      Excel MVP 2011-2014

      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

      Comment


      • #4
        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?

        Comment


        • #5
          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
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            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!

            Comment


            • #7
              Re: Find Date of Monday previous to Today

              That's the correct way to do it indeed.

              Good luck with your Excel work.
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment

              Working...
              X