Cell contents to change periodically

[javedmati]

Hi,

We have an excel file to monitor weekly data. I would like to perform the following action for this file.

Evry Tuesday of the week, the content of cell A1 should be changed to

"DATA FOR WEEK ENDING" + Date of Friday for that week.

For e.g. if today is 10th March 2009, then the value in cell A1 should be

DATA FOR WEEK ENDING 13th March 2009

Here 10th March 2009 is Tuesday and 13th March 2009 is Friday of the same week.

Contents in cell A1 [ "DATA FOR WEEK ENDING 13th March 2009" ] should remain till the next Monday i.e. till 16th March 2009

And on the next day, i.e. on 17th March 2009 - Tuesday the value in cell A1 should be changed to

DATA FOR WEEK ENDING 20th March 2009

I hope I have not confused anyone.

Cheers guys.

Regards,
Javed.

 

[Andrew Poulsom]

Try:

="DATA FOR WEEK ENDING " & TEXT(TODAY()+6-WEEKDAY(TODAY())-(7*(WEEKDAY(TODAY())<3)),"dd mmmm yyyy")

 

[javedmati]

Heyyy....

It works buddy !

Cheers,
Javed.