Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,
    I'm using a formula that Han's gave me years ago to determine the week ending date for an agency. In the original request I needed the date returned to always be a Friday. Now I need it to always return a Tuesday. I'm not sure where to start. This is the formula I'm using:

    WEDate: [Service_date]+6-Weekday([Service_date])

    WEDate needs to equal the "Tuesday" for the week that [Service_date] falls in. So if [Service_Date] = 2/27/2009, the result should show WEDate = 3/3/2009

    Thanks,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Leesha

    Have you tried

    WEDate: [Service_date]+10-Weekday([Service_date])
    Jerry

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Jerry!

    I just gave that a try and it's still returning Friday.

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Leesha' post='762400' date='28-Feb-09 13:40']WEDate needs to equal the "Tuesday" for the week that [Service_date] falls in. So if [Service_Date] = 2/27/2009, the result should show WEDate = 3/3/2009[/quote]
    That's strange - if Service_Date = 27-Feb-2009 (a Friday), "the Tuesday for the week that Service_Date falls in" was 24-Feb-2009.
    Do you mean "the next Tuesday" instead of "the Tuesday for the week that Service_Date falls in"?
    If so, try

    =[Service_Date]+(10-Weekday([Service_Date])) Mod 7

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans,
    My error..........yes I mean the next Tuesday.................

    I tried the formula you gave me but I'm still off. For example, a [service_date] of 12/31/08 is returning a weekending date of 1/3/2009 when it should be 1/6/2009

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    You're formula is totally correct (of course )...............the 12/31/09 date that wasn't coming up correct when I tested had a year end date of 2209 (data entry error obviously). When I went back and randomly checked the formula against various dates over the past 5 years in this database the were all correct except for the most current december ones...............I couldn't figure out why and then noticed the year error.

    Thanks and sorry to frustrate you!
    Leesha

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Leesha' post='762419' date='28-Feb-09 15:55'][/quote]
    Glad you found the cause. Now you've got a working formula *and* you've corrected some data entry errors!

Posting Permissions

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