Displaying Tuesday's date throughout the week
 

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

Try this:

=TODAY()-WEEKDAY(TODAY())+3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

Ron,
Thanks for your help. Unfortunately, when TODAY() = 1/7/2007, the date in
cell A1 changes to 1/9/2007. The date in cell A1 should not change until it
is Tuesday (e.g., 1/9/2007, 1/16/2007, etc.). Please refer to my original
question.
Thanks again,
Bob


"Ron Coderre" wrote:

Try this:

=TODAY()-WEEKDAY(TODAY())+3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

OK....how about this?

=TODAY()-CHOOSE(WEEKDAY(TODAY()),5,6,0,1,2,3,4)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Ron,
Thanks for your help. Unfortunately, when TODAY() = 1/7/2007, the date in
cell A1 changes to 1/9/2007. The date in cell A1 should not change until it
is Tuesday (e.g., 1/9/2007, 1/16/2007, etc.). Please refer to my original
question.
Thanks again,
Bob


"Ron Coderre" wrote:

Try this:

=TODAY()-WEEKDAY(TODAY())+3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

Bob,

Does:

=TODAY()-WEEKDAY(TODAY()+5,1)+1

Do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Bob" wrote in message
...
I'm trying to create a formula in cell A1 that will always display the
date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would
prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

On Tue, 2 Jan 2007 08:26:00 -0800, Bob wrote:

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob


=A5-WEEKDAY(A5-2)+1

Use a date in A5 for testing.

Substitute TODAY() if it does the job for you.


--ron

Displaying Tuesday's date throughout the week
 

Ron,
That did the trick! Thanks!!!
Regards,
Bob


"Ron Coderre" wrote:

OK....how about this?

=TODAY()-CHOOSE(WEEKDAY(TODAY()),5,6,0,1,2,3,4)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Ron,
Thanks for your help. Unfortunately, when TODAY() = 1/7/2007, the date in
cell A1 changes to 1/9/2007. The date in cell A1 should not change until it
is Tuesday (e.g., 1/9/2007, 1/16/2007, etc.). Please refer to my original
question.
Thanks again,
Bob


"Ron Coderre" wrote:

Try this:

=TODAY()-WEEKDAY(TODAY())+3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

Sandy,
Interesting solution. It took me a few minutes to understand how the logic
works, but now that I understand it, it is very slick!
Thanks a lot!
Bob


"Sandy Mann" wrote:

Bob,

Does:

=TODAY()-WEEKDAY(TODAY()+5,1)+1

Do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Bob" wrote in message
...
I'm trying to create a formula in cell A1 that will always display the
date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would
prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob

Displaying Tuesday's date throughout the week
 

Ron,
Your solution is very similar to Sandy Mann's except that her solution adds
5 days, whereas your solution subtracts 2 days. Either way, you get the same
result.
Thanks for your help!
Bob


"Ron Rosenfeld" wrote:

On Tue, 2 Jan 2007 08:26:00 -0800, Bob wrote:

I'm trying to create a formula in cell A1 that will always display the date
of the Tuesday of the current week. For example, from 1/2/2007 through
1/8/2007, cell A1 would always display 1/2/2007; from 1/9/2007 through
1/15/2007, cell A1 would always display 1/9/2007, etc.
I have tried using a combination of the WEEKDAY and MOD functions, but I
have been unable to come up with a formula that works. Also, I would prefer
NOT to use a UDF if at all possible.
Any help would be greatly appreciated.
Thanks, Bob


=A5-WEEKDAY(A5-2)+1

Use a date in A5 for testing.

Substitute TODAY() if it does the job for you.


--ron

Displaying Tuesday's date throughout the week
 

On Tue, 2 Jan 2007 21:39:03 -0800, Bob wrote:

Ron,
Your solution is very similar to Sandy Mann's except that her solution adds
5 days, whereas your solution subtracts 2 days. Either way, you get the same
result.
Thanks for your help!
Bob



You're welcome.

Thanks for the feedback.

--ron