| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sum of a named range with values based on vlookup results | Excel Worksheet Functions | |||
| Sum values in columns based on values in named range | Excel Worksheet Functions | |||
| Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Define a range based on another named range | Excel Worksheet Functions | |||
Linear Mode
