automatic date change in VBA?

[Legacy 237453]

Hi

I need the following text in cell B:7 to change depending on the date.

"SAP F&R Poland Milestones for Tuesday the 9th of March 2013"

The thing is i need the date to read the date for the previous day? The reason being is that im reporting on the milestones for the previous day.
I guess im looking for a way to have the "Tuesday", "9th", "March" and "2013" to change automatically?

Thanks for reading!
Cheers
Craig

 

[Norie]

Perhaps this.

="SAP F&R Poland Milestones for " & TEXT(TODAY()-1, "dddd d mmmm yyyy")

If you want the ordinals th, nd etc then there is a formula but I don't have it handy.

Found it.

="SAP F&R Poland Milestones for "&SUBSTITUTE(TEXT(TODAY()-1,"dddd ""the"" dzz mmmm yyyy"),"zz",MID("thstndrdth",MIN(9,2*RIGHT(DAY(TODAY()-1))*(MOD(DAY(TODAY()-1)-11,100)>2)+1),2))

 

[Legacy 237453]

Thank you so much that works perfectly!