Formula Based on Values in Named Range Q
 

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

Formula Based on Values in Named Range Q
 

Holiday dates in F:F

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

On Jan 2, 5:14*am, Seanie wrote:
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

Formula Based on Values in Named Range Q
 

On Mon, 2 Jan 2012 03:14:19 -0800 (PST), Seanie wrote:

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.

Formula Based on Values in Named Range Q
 

^^^^ 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

Formula Based on Values in Named Range Q
 

On Jan 2, 10:03*am, Seanie wrote:
^^^^ 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

Formula Based on Values in Named Range Q
 

On Mon, 2 Jan 2012 08:03:04 -0800 (PST), Seanie wrote:

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

Glad to help. Thanks for the feedback.