Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching in the list of dates
I hope that someone can help me with the following:
When filling in a specific date I have to tjeck for holidays which are filled in, in another sheet. Is there a special function for this? -- Regards Louise |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching in the list of dates
Try the following function
=NETWORKDAYS(A2,B2,C3:C14) Where cell A2 holds the start date (or the lower date) B2 holds the end date and the Column C from range C2:C14 holds the holidays. To use this function you must have the Analysis ToolPack add-in installed. Click Tools-- Addins -- Select Analysis Toolpack. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching in the list of dates
=ISNUMBER(MATCH(date_cell,holiday_lits,0))
will return TRUE if it is a holiday -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "louise" wrote in message ... I hope that someone can help me with the following: When filling in a specific date I have to tjeck for holidays which are filled in, in another sheet. Is there a special function for this? -- Regards Louise |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching in the list of dates
Hi Bob,
Looks like a nice solution but I do not have ISNUMBER and MATCH, although I have added Ananlysis Toolpack. Do you know how/where I can get the functions? -- Regards Louise "Bob Phillips" skrev: =ISNUMBER(MATCH(date_cell,holiday_lits,0)) will return TRUE if it is a holiday -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "louise" wrote in message ... I hope that someone can help me with the following: When filling in a specific date I have to tjeck for holidays which are filled in, in another sheet. Is there a special function for this? -- Regards Louise |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching in the list of dates
Both those functions are in excel.
If you're not using an English version of excel, then that could be the problem. You may want to share the excel language you're using. Or you could try this. Create a new worksheet Hit alt-f11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window type this and hit enter: range("a1").formula = "=isnumber(b1)" then type this and hit enter: range("a2").formula = "=match(b2,c:c,0)" Then back to that worksheet and look at the formulas in A1 and A2 (ignore the values returned). louise wrote: Hi Bob, Looks like a nice solution but I do not have ISNUMBER and MATCH, although I have added Ananlysis Toolpack. Do you know how/where I can get the functions? -- Regards Louise "Bob Phillips" skrev: =ISNUMBER(MATCH(date_cell,holiday_lits,0)) will return TRUE if it is a holiday -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "louise" wrote in message ... I hope that someone can help me with the following: When filling in a specific date I have to tjeck for holidays which are filled in, in another sheet. Is there a special function for this? -- Regards Louise -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching in the list of dates
Hi Dave,
-Thanks a lot (and the rest of you guys).... -- Regards Louise "Dave Peterson" skrev: Both those functions are in excel. If you're not using an English version of excel, then that could be the problem. You may want to share the excel language you're using. Or you could try this. Create a new worksheet Hit alt-f11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window type this and hit enter: range("a1").formula = "=isnumber(b1)" then type this and hit enter: range("a2").formula = "=match(b2,c:c,0)" Then back to that worksheet and look at the formulas in A1 and A2 (ignore the values returned). louise wrote: Hi Bob, Looks like a nice solution but I do not have ISNUMBER and MATCH, although I have added Ananlysis Toolpack. Do you know how/where I can get the functions? -- Regards Louise "Bob Phillips" skrev: =ISNUMBER(MATCH(date_cell,holiday_lits,0)) will return TRUE if it is a holiday -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "louise" wrote in message ... I hope that someone can help me with the following: When filling in a specific date I have to tjeck for holidays which are filled in, in another sheet. Is there a special function for this? -- Regards Louise -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searching between dates | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
Searching on Dates | Excel Worksheet Functions | |||
Searching dates | Excel Programming | |||
searching for a range of dates | Excel Programming |