Back Dating (Counting Backwards)

[Guest]

I was trying to use the following formula to come up with a date 40 work days
prior to the date specified in cell AN2. The following formula works great
except I work 4X10's (Mon - Thur) and this formaula is counting Friday.

=IF(AN2="","",WORKDAY(AN2+(WEEKDAY(AN2)=5),-40,holidays))

Is there a variation of this formula that will do the same thing except just
count Mon thru Fri?

Or is there a better formula that will take care of my problem?

Thank you,

Aviator

 

[Bernie Deitrick]

Aviator,

Simplest would be to add every Friday to your list of holidays....

HTH,
Bernie
MS Excel MVP

 

[Guest]

That is not going to work in this situation. Do you have any other ideas?

 

[Bernie Deitrick]

Aviator,

The only other alternative is to roll your own function using VBA. But why
can't you add Fridays to your list of holidays? You could make up your own
list that includes holidays and name it something else.....

HTH,
Bernie
MS Excel MVP

 

[Myrna Larson]

Assuming you mean "just count Mon thru THU" instead of "Mon thru Fri", one
quick fix would be to simply add all of the Fridays to your list of holidays.
Then the formula is just

=IF(AN2="","",WORKDAY(AN2,-40,Holidays))

Otherwise, you would need a custom VBA function, which will definitely
recalculate more slowly. That could be a concern if you use the function a lot
on the worksheet.

 

[Myrna Larson]

Exactly WHY won't it work? Holidays is simply a list of days off in addition
to Saturdays and Sundays.