Back Dating (Counting Backwards)
 

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

Aviator,

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

HTH,
Bernie
MS Excel MVP

"Aviator" wrote in message
...
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

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



"Bernie Deitrick" wrote:

Aviator,

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

HTH,
Bernie
MS Excel MVP

"Aviator" wrote in message
...
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

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

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



"Bernie Deitrick" wrote:

Aviator,

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

HTH,
Bernie
MS Excel MVP

"Aviator" wrote in message
...
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

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.

On Wed, 9 Feb 2005 09:05:14 -0800, "Aviator"
wrote:

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

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

On Wed, 9 Feb 2005 09:43:05 -0800, "Aviator"
wrote:

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



"Bernie Deitrick" wrote:

Aviator,

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

HTH,
Bernie
MS Excel MVP

"Aviator" wrote in message
...
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