I have the formula below that returns the string "Sales for w/e
19/10/08" - depending of course what the value Today()-1 is. How could
I modify that if Today()-1 is anything other than a Sunday value, it
would return the date of the previous Sunday to the one I am now in?
E.g. Today()-1 is 22/10/08 but I would want the date returned as
19/10/08

="Sales for w/e "&TEXT((TODAY()-1),"dd/mm/yy")

Give this a try...

="Sales for w/e "&TEXT(TODAY()-WEEKDAY(TODAY()-1),"dd/mm/yy")

--
Rick (MVP - Excel)


"Seanie" wrote in message
...
I have the formula below that returns the string "Sales for w/e
19/10/08" - depending of course what the value Today()-1 is. How could
I modify that if Today()-1 is anything other than a Sunday value, it
would return the date of the previous Sunday to the one I am now in?
E.g. Today()-1 is 22/10/08 but I would want the date returned as
19/10/08

="Sales for w/e "&TEXT((TODAY()-1),"dd/mm/yy")

Hi,

Try this

="Sales for w/e "&TEXT((TODAY()-WEEKDAY(TODAY())+1),"dd/mm/yy")

Mike

"Seanie" wrote:

I have the formula below that returns the string "Sales for w/e
19/10/08" - depending of course what the value Today()-1 is. How could
I modify that if Today()-1 is anything other than a Sunday value, it
would return the date of the previous Sunday to the one I am now in?
E.g. Today()-1 is 22/10/08 but I would want the date returned as
19/10/08

="Sales for w/e "&TEXT((TODAY()-1),"dd/mm/yy")

On Thu, 23 Oct 2008 01:12:35 -0700 (PDT), Seanie
wrote:

I have the formula below that returns the string "Sales for w/e
19/10/08" - depending of course what the value Today()-1 is. How could
I modify that if Today()-1 is anything other than a Sunday value, it
would return the date of the previous Sunday to the one I am now in?
E.g. Today()-1 is 22/10/08 but I would want the date returned as
19/10/08

="Sales for w/e "&TEXT((TODAY()-1),"dd/mm/yy")

A general formula would be:

=A1+1-WEEKDAY(A1)

So, in your string, substitute for A1, TODAY()-1.

e.g.

="Sales for w/e "&TEXT((TODAY()-WEEKDAY(TODAY()-1)),"dd/mm/yy")

--ron

Thanks Guys, I think Rick yours is the one that actually returns the
correct expected value