Hi,

I currently use this formula to populate teh nearest Friday to today:

=TEXT(A13+LOOKUP(WEEKDAY(A13),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy")

How do I change the formula so that it will caluclate the nearest Sunday?

Thanks for the help.

=TEXT(A13+LOOKUP(WEEKDAY(A13),{1,2,3,4,5,6,7},{0,-1,-2,-3,3,2,1}),"mm/dd/yyyy")


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"JR" wrote in message
...
Hi,

I currently use this formula to populate teh nearest Friday to today:

=TEXT(A13+LOOKUP(WEEKDAY(A13),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy")

How do I change the formula so that it will caluclate the nearest Sunday?

Thanks for the help.

Try something like this:

=TEXT(A13+CHOOSE(WEEKDAY(A13,2),-1,-2,-3,3,2,1,0),"mm/dd/yyy")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JR" wrote:

Hi,

I currently use this formula to populate teh nearest Friday to today:

=TEXT(A13+LOOKUP(WEEKDAY(A13),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy")

How do I change the formula so that it will caluclate the nearest Sunday?

Thanks for the help.

On Fri, 7 Apr 2006 11:36:01 -0700, JR wrote:

Hi,

I currently use this formula to populate teh nearest Friday to today:

=TEXT(A13+LOOKUP(WEEKDAY(A13),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy")

How do I change the formula so that it will caluclate the nearest Sunday?

Thanks for the help.


You could also use the formula:

=A1-WEEKDAY(A1)+1+7*(WEEKDAY(A1)4)

and format the cell as a date.


--ron

Hi,

For nearest Friday, you can use:
=A1+4-WEEKDAY(A1+5)

Also, for nearest Sunday
=A1+4-WEEKDAY(A1+3)

and format the cells as a date

Regards,

Daniel M.

"JR" wrote in message
...
Hi,

I currently use this formula to populate teh nearest Friday to today:

=TEXT(A13+LOOKUP(WEEKDAY(A13),{1,2,3,4,5,6,7},{-2,-3,3,2,1,0,-1}),"mm/dd/yyyy")

How do I change the formula so that it will caluclate the nearest Sunday?

Thanks for the help.

On Sat, 8 Apr 2006 12:46:20 -0400, "Daniel.M"
wrote:

For nearest Friday, you can use:
=A1+4-WEEKDAY(A1+5)

Also, for nearest Sunday
=A1+4-WEEKDAY(A1+3)

Neat!
--ron

Thanks Ron.
Have a nice day. :-)

Daniel M.

"Ron Rosenfeld" wrote in message
...
On Sat, 8 Apr 2006 12:46:20 -0400, "Daniel.M"

wrote:

For nearest Friday, you can use:
=A1+4-WEEKDAY(A1+5)

Also, for nearest Sunday
=A1+4-WEEKDAY(A1+3)

Neat!
--ron