Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Return Monday Of Given Week

  1. #1
    Join Date
    16th February 2007
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th February 2007
    Posts
    50

    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:

    VB:
    =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!

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    16th February 2007
    Posts
    5

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Return Monday Of Given Week

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th February 2007
    Posts
    50

    Re: Return Monday Of Given Week

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th February 2007
    Posts
    5

    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))

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th February 2007
    Posts
    50

    Re: Return Monday Of Given Week

    I would have used:

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

    But to each his own!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th February 2012
    Posts
    2

    Re: Return Monday Of Given Week

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th March 2010
    Posts
    1,651

    Re: Return Monday Of Given Week

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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th February 2012
    Posts
    2

    Re: Return Monday Of Given Week

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. VBA To return week number?
    By Grasshoppa in forum EXCEL HELP
    Replies: 7
    Last Post: December 21st, 2012, 18:38
  2. Return Corresponding Data For Week Number
    By mswuk in forum EXCEL HELP
    Replies: 2
    Last Post: January 27th, 2008, 08:27
  3. look up day in calendar array return week
    By kai_ge in forum EXCEL HELP
    Replies: 4
    Last Post: August 10th, 2006, 21:54
  4. Return Weekday, or Day of Week
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: October 3rd, 2005, 17:07
  5. Return Week Number
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: October 3rd, 2005, 16:26

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