Formula Based on Values in Named Range Q

[Seanie]

I have the following formula that produces a Date / Time value
BF1+(540/1440) eg 02/01/2012 09:00

What I wish to do is modify above that if BF1 equals any dates listed
in a named range "Holidays" then the formula would change to
BF1+1+(540/1440)

Its the bit on looking up values in the named range that I can't work
out

 

[Don Guillett]

Holiday dates in F:F

=IF(ISNA(MATCH(bf1,F:F,0)),D1,D1+1)+(540/1440)
===========

 

[Ron Rosenfeld]

I have the following formula that produces a Date / Time value
BF1+(540/1440) eg 02/01/2012 09:00

What I wish to do is modify above that if BF1 equals any dates listed
in a named range "Holidays" then the formula would change to
BF1+1+(540/1440)

Its the bit on looking up values in the named range that I can't work
out

In general:

=BF1+SUMPRODUCT(--(Holidays=BF1)) + 540/1440

But also take a look at the WORKDAY function.

 

[Seanie]

^^^^ Thanks Guys

Don your formula =IF(ISNA(MATCH(bf1,F:F,0)),D1,D1+1)+(540/1440) ,
produces 01/01/12 09:00, not what I expected as BF1= TODAY() i.e.
02/01/2012, so I expected 03/01/12 09:00. Whats the relevance of D1 in
the formula?


Ron, your formula =BF1+SUMPRODUCT(--(Holidays=BF1)) + 540/1440 works a
treat

 

[Don Guillett]

^^^^ Thanks Guys

Don your formula  =IF(ISNA(MATCH(bf1,F:F,0)),D1,D1+1)+(540/1440) ,
produces 01/01/12 09:00, not what I expected as BF1= TODAY() i.e.
02/01/2012, so I expected 03/01/12 09:00. Whats the relevance of D1 in
the formula?

Ron, your formula =BF1+SUMPRODUCT(--(Holidays=BF1)) + 540/1440 works a
treat

Change d1 to bf1 or use Rons

 

[Ron Rosenfeld]

Ron, your formula =BF1+SUMPRODUCT(--(Holidays=BF1)) + 540/1440 works a
treat

Glad to help. Thanks for the feedback.