Text String Formula Q

[Seanie]

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")

 

[Rick Rothstein]

Give this a try...

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

 

[Mike H]

Hi,

Try this

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

Mike

 

[Ron Rosenfeld]

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

 

[Seanie]

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