Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Date Based on Other Dates

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

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

  • #2
    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, 10:11.
    Kris

    ExcelFox

    Comment


    • #3
      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.

      Comment


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

        ExcelFox

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Date Formula For Specific Days

            Hi Kris,

            I just get #N/A

            Tim.

            Comment


            • #7
              Re: Date Formula For Specific Days

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

              Comment


              • #8
                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.

                Comment


                • #9
                  Re: Date Formula For Specific Days

                  See attachment, I have removed most of the worksheet to enable me to upload it.
                  Attached Files
                  Last edited by Timbo; December 15th, 2006, 21:22.

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Date Formula For Specific Days

                      In your example sheet you don't have a number in A8 to say which week in the month you want to return.
                      Regards,
                      Batman.

                      Comment


                      • #12
                        Re: Date Formula For Specific Days

                        The first, second, third and fourth Tuesdays in the month refer to A8. If there are five in the accounting period the last Tuesday.

                        Comment


                        • #13
                          Re: Date Formula For Specific Days

                          There should be a number 3 which equals the third Friday of the month.

                          Have to go to a meeting now, hopefully resume later.

                          Tim.

                          Comment


                          • #14
                            Re: Date Formula For Specific Days

                            The cell references you gave Kris referred to column A (A8 and A9), but in your actual sheet the entries are in column B (B8 and B9), so the formula needs to be changed to reflect the different location:

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

                            Also, cell B9 displays "Friday", but this is achieved by way of number formatting. Excel is actually holding a number/date (the formula bar shows 06/01/1900) so the VLookup in the formula is returning an error, because it needs to look for a text string. If you delete the date from B9, change the cell format to General and type in the text "Friday" the formula will work (or at least it will return a value).

                            I'm still not sure whether, when B8 contains 3, B11 should return "Fri 15/12/06" (the third Friday in that month because B8 contains 3) or "Fri 29/12/06" (the last Friday in the month because December 2006 is a 5-week month).
                            Regards,
                            Batman.

                            Comment


                            • #15
                              Re: Date Formula For Specific Days

                              Hi Batman,

                              Just got back in time to go go out again.

                              I have made the ammendments and got it working, many thanks.

                              Tim.

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X