Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 7 1 2 3 5 ... LastLast
Results 1 to 10 of 64

Thread: Date Based on Other Dates

  1. #1
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Date Based on Other Dates

    In A8 I have a number 1 - 5.

    This identifies the weeks in a month 1 - 4 for four week months. The 5 is for a five week accounting month.

    In A9 I have a day of the week it could be any day formatted as dddd

    In A11 I have the 1st day of the month 01/01/2007 formatted as ddd/yyyy

    I need a formulae that will look at the number in A8 i.e. 2
    Then look at the day in A9 i.e. Tuesday
    Then look at the month in A11 i.e. January

    Based on all of that I want it to return the date of the 2nd Tuesday in January 2007 in B11.

    In a five week month I need it to return the last Tuesday of the month.

    Weeks per month are show in starting in D11 for January.

    Any help appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Date Formula For Specific Days

    Not tested, but try,

    =(A11-WEEKDAY(A11)+1+((A8-1)*7)+2)

    Excel Dates & Times
    Last edited by Dave Hawley; December 16th, 2006 at 10:11.

  3. #3
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Date Formula For Specific Days

    Hi Kris,

    Thanks for helping on this.

    Your formula returns Tue 26/12/06 not the 2nd Tuesday of January 2007, and if I change B8 to 1 and B9 to Friday the date doesn't change to the first Friday of January 2007.

    Tim.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Date Formula For Specific Days

    Try,

    =(A11-WEEKDAY(A11)+1+((A8-1)*7)+VLOOKUP(A9,{"Sunday",1;"Monday",2;"Tuesday",3;"Wednesday",4;"Thursday",5;"Friday",6;"Saturday",7},2,0)-1)

    HTH

  5. #5
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    3,368

    Re: Date Formula For Specific Days

    Timbo,

    In a five week month I need it to return the last Tuesday of the month.
    Do you mean that if there are 5 weeks in the month, you want the formula to ignore the value entered in A8 and always return the last occurrence of the selected day in that particular month?
    Regards,
    Batman.

  6. #6
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Date Formula For Specific Days

    Hi Kris,

    I just get #N/A

    Tim.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    3,368

    Re: Date Formula For Specific Days

    It returns the correct date for me.
    Regards,
    Batman.

  8. #8
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Date Formula For Specific Days

    Hi Batman,

    Yes, whichever day of the week it is, if there are five of them in the month then show the date of the last Monday or Tuesday dependent on cells A8 and A9.

    Tim.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Date Formula For Specific Days

    See attachment, I have removed most of the worksheet to enable me to upload it.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Timbo; December 15th, 2006 at 21:22.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    3,368

    Re: Date Formula For Specific Days

    But A8 holds the week number that you want to return, e.g. 2 tells the formula that you want to return the second Tuesday in the month. If A8 holds 2 and the month has 5 weeks, do you want to return the second Tuesday in the month or the last Tuesday in the month (which would effectively mean ignoring A8 if there were 5 weeks in the month)?
    Regards,
    Batman.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Compare Dates To Date Range & Return Date Based On Outcome
    By [email protected] in forum Excel General
    Replies: 2
    Last Post: July 1st, 2008, 09:40
  2. Count Dates & String Dates After Given Date
    By p45cal in forum Excel General
    Replies: 4
    Last Post: May 14th, 2008, 21:43
  3. Replies: 2
    Last Post: April 16th, 2008, 12:29
  4. Replies: 4
    Last Post: February 28th, 2008, 10:54
  5. Add To Dates Based On Criteria & Flag Overdue Dates
    By Big Dog Dad in forum Excel General
    Replies: 10
    Last Post: January 9th, 2007, 05:22

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