# Thread: Determining Day of the Week

1. 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. Hi Leesha

Have you tried

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

3. Hi Jerry!

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

Leesha

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