Results 1 to 7 of 7

Thread: date function

  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to find a date formula that would give me the date of the 2nd Wednesday of each month. I was able to narrow it down to this formula but some dates are correct and some dates are off.

    C3= Thursday, January 8, 2009 D3= 5

    =IF(D3<4.3-D3,11-D3)+C3.

    Thank you.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Altering the formula from Post 758306

    =C3-DAY(C3)+1+MOD(4-WEEKDAY(C3-DAY(C3)+1),7)+7

  3. #3
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula works great but I tried to evaluate your formula but it appears that I need assistance to put them in laymen terms or me?

    TIA.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you really want to know the mathematics behind the formula?

  5. #5
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    would it burn any more of my brain's cells? apparently you know I don't have that much left. btw, congratulations on the new Woody's. It looks great but the people behind it that makes it much better. Danke!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    OK, here you go:

    Let's take Sunday, February 22, 2009 as an example

    C3 = the date (22-Feb-2009).
    Day(C3) = the day of the month (22).
    C3-DAY(C3) is 22 days before 22-Feb-2009 i.e. the last day of the previous month (31-Jan-2009).
    C3-DAY(C3)+1 is one day after that, i.e. the first day of the current month (01-Feb-2009).
    WEEKDAY(C3-DAY(C3)+1) is the day of the week of that date, where Sunday = 1, Monday = 2, , Saturday = 7. In our example, it is 1.
    4-WEEKDAY(C3-DAY(C3)+1) is 3 for Sunday, 2 for Monday, , -3 for Saturday. In our example, it is 3.
    MOD(4-WEEKDAY(C3-DAY(C3)+1),7) calculates the remainder of that number after integer division by 7; this is 3 for Sunday, 2 for Monday, 1 for Tuesday, 0 for Wednesday, 6 for Thursday, 5 for Friday and 4 for Saturday. In our example, it is 3.
    This is the number of days you have to add to the first of the month to end up at the first Wednesday in the month.
    So C3-DAY(C3)+1+MOD(4-WEEKDAY(C3-DAY(C3)+1),7) is the first Wednesday (04-Feb-2009)
    Finally, 7 is added to this to give the second Wednesday in the month:
    =C3-DAY(C3)+1+MOD(4-WEEKDAY(C3-DAY(C3)+1),7)+7
    In our example, this is 11-Feb-2009.

  7. #7
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your explanation. I'll take an aspirin now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •