Excel 2007 : today()-2 working days, in with other text

Hi,

I have the following formula at present:

="ComplianceTracker for 2 days prior: ie"&" "&TEXT(TODAY(),"ddd dd-mmm")&" "&"checking for"&" "&TEXT(TODAY()-2,"ddd dd-mmm")

Which currently shows the following:

ComplianceTracker for 2 days prior: ie Thu 12-May checking for Tue 10-May

However I ony want to count dates that fall Mon-Fri. So:
If today() is a Mon, it should show Thursdays date
If today() is a tuesday, it should show Friday
Then if today() is Wednesday, it should show Monday.

I've tried integrating the WORKDAY function in several places, but nothing seems to work.

Thanks in advance.

One way, maybe:

="ComplianceTracker for 2 days prior: ie"&" "&TEXT(TODAY(),"ddd dd-mmm")&" "&"checking for"&" "&TEXT(IF(WEEKDAY(TODAY())>3,TODAY()-2,TODAY()-4),"ddd dd-mmm")


Regards

What if TODAY is sunday?

It's part of an IF statement:

TEXT ... (IF(WEEKDAY(TODAY())>3,TODAY()-2,TODAY()-4))

It's checking what weekday it is and adjusting the date appropriately


You can test using a cell rather than TODAY()

="ComplianceTracker for 2 days prior: ie"&" "&TEXT(B3,"ddd dd-mmm")&" "&"checking for"&" "&TEXT(IF(WEEKDAY(B3)>3,B3-2,B3-4),"ddd dd-mmm")

If B3 has 02/05/2011, you get:

ComplianceTracker for 2 days prior: ie Mon 02-May checking for Thu 28-Apr


Regards