Announcement

Collapse
No announcement yet.

Return Monday Of Given Week

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Return Monday Of Given Week



    I have seen this before and once figured it out, but I have not had any luck this time.

    I'm looking for a formula that will reference a cell that contains a date and will then return the date of the Monday of the given date's week.

    If I gave it a cell that contained 2/15/07 it would return 2/12/07.

    Probably a simple question but I think I must not be working on all cylinders. =) Thank you much

  • #2
    Re: Return Monday Of Given Week

    I have recently written a simple formula to achieve this same result but for finding the next Sunday following a date. It is as follows:

    Code:
    =IF(WEEKDAY(D18)=1,D18,D18+(8-WEEKDAY(D18)))

    D18 is obviously the source of the random date. When using the WEEKDAY function, Excel resolves the date to an integer that represents the days of the week. Sunday is 1, Monday is 2, etc.

    So, this basically says, If the date is a Sunday, than make the date the same, else take Excels numeric value for that date and add it to (8 - (the weekday value of that date).

    Hope this gets you started... You will have to subtract from your date of course to get the Monday beginning that week! Good Luck!

    Comment


    • #3
      Re: Return Monday Of Given Week

      that is definitely a better way of looking at it than what i was messing with. thank you for your reply - much appreciated. oh and go Blue Devils!

      Comment


      • #4
        Re: Return Monday Of Given Week

        Jerid, please don't use code tags round formulas, only round VBA.
        .

        Comment


        • #5
          Re: Return Monday Of Given Week

          Sorry... thought they went around all code! Will make note of it! Thanks.

          Comment


          • #6
            Re: Return Monday Of Given Week

            Ok just in case someone else stumble upon this same question. The following worked for me. Very simple and i really didn't need to post but it's just been one of those days. =)

            =IF(WEEKDAY(A1)=1,A1-6,A1-(WEEKDAY(A1)-2))

            Comment


            • #7
              Re: Return Monday Of Given Week

              I would have used:

              IF(WEEKDAY(A1)=2, A1,A1-(8-WEEKDAY(A1))

              But to each his own!

              Comment


              • #8
                Re: Return Monday Of Given Week

                =today()-(weekday(today())-2)

                Comment


                • #9
                  Re: Return Monday Of Given Week

                  of course:
                  PHP Code:
                  =A1-(WEEKDAY(A1)-2
                  Last edited by snb; February 28th, 2012, 23:00. Reason: change frc tags to php which reflects exactly the editing window in Excel adn doesn't add confusin information on array formu

                  Comment


                  • #10
                    Re: Return Monday Of Given Week

                    Which is the same as mine...but mien is variable

                    Comment


                    • #11


                      Re: Return Monday Of Given Week

                      The thread is already solved and is actually 5 years old, I dont think we need more solutions here...

                      Closed.
                      Ger

                      Check out our new reputation system. Click on the "star" under the post!
                      _______________________________________________

                      There are 10 types of people in the world. Those that understand Binary and those that dont.

                      Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                      The BEST Lookup function of all time

                      Dynamic Named Ranges are your bestest friend

                      _______________________________________________

                      Comment

                      Working...
                      X