Returning a value based on business days issue

Hi,

I am attempting to create a formula that will allow me to come back with a value if a cell is a business day with respect to the day the formula is run.

So for example,

A1 through A200 have dates

I want to create a function that states something to the effect of:

If A1 equals today and is a business day then "value that I choose"
If A1 equals today minus 1, then "value that I choose"
If A1 equals today minus 2, then "value that choose"

At all times the value of the day minus any number can only be Monday through Friday. Weekends and holidays should be excluded.

Further clarification, if today was a Monday then by stating A1 equals today minus 1, should designate the previous Friday, not Sunday.

If this was just a simple formula that did not take into consideration only business days, then I would be set, but I do not know how to only use business days for this type of scenario.

Thanks for any help.

Let me see if I can clarify a bit.

Column A has dates. Let's just say all dates are 4/15, 4/14, or 4/11 (Therefore no dates will ever fall on a weekend in column A)

I want to create a function that allows me to come back with a value based on the date in column A with respect to the day I run the formula.

So column A has a date of 4/15 I need it to come back with the word "Blue"
If column A has a date of 4/14 I need it to come back with the word "Red"
If column A has a date of 4/11/14 I need it to come back with the word "Green"

I want to be able to do this on a day to day basis where column A will always only include weekdays with no weekend.

Hope that makes it a little more clear on what I am trying to do. Sorry if I am not as clear since my knowledge of excel is still growing.

See if this will work. the basic idea is to determine the starting position and then calculate the number of workdays from that starting position. That number can be used with in index to generate your dynamic values.


DynamicBizDayIssue.xlsx

See the worksheet I attached to get a better idea of what I am trying to accomplish.

Column B contains the formula I want to manipulate. Red is a description of what I am having trouble with in column C.

It's just a value I am using based on the days from the current day. Due for 3 would be the current date. Due for 2 would be the previous day. Due for 1 would be associated with 2 days prior to the current date. The issue is getting the "Due for number" to correlate to a business day sequence and not a full week sequence. That is why Due for 1 in my example is the issue. I am trying to correlate Due for 1 to 4/11/14. I want to be able to apply the solution to any day of the week for any number of days in column A.

I think I just figured it out after messing around myself.

This is what is working for me now:

=IF(WORKDAY(TODAY(),0,)=A2,"Due for 3",IF(WORKDAY(TODAY(),-1,)=A2,"Due for 2",IF(WORKDAY(TODAY(),-2,)=A2,"Due for 1")))