Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Buisness Days Help
Hi everyone,
I am looking for some help on the following. On business days (Yes), it would automatically input a value (400) under Daily Target Currently, I have a worksheet that looks like the following... Date Day Business Day Daily Target 12/1/2006 Friday Yes 400 12/2/2006 Saturday No 0 12/3/2006 Sunday No 0 12/4/2006 Monday Yes 400 etc.. Is it possible to automate excel to recognize 12/1/2006 as a business day, and assign the value of 400 under Daily Target. If it is possible, can excel recognize holidays as well? 12/25/2006 Monday (Holiday) ~ not a business day? I am open to any ideas, thanks, PJS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Buisness Days Help
You can use the NETWORKDAYS function which is part of Analysis ToolPak addin
[to install TOOLS addins tick Analysis Toolpak box] Then, assuming your date is in cell A1 =IF(NETWORKDAYS(A1,A1,holidays),"Yes","No") holidays is a named range where you should list your holiday dates. Alternatively you can just refer directly to a worksheet range, e.g. =IF(NETWORKDAYS(A1,A1,C$1:C$10),"Yes","No") where your holiday dates are listed in C1:C10 alternatively, without using NETWORKDAYS =IF(AND(ISNA(MATCH(A1,C$1:C$10,0)),WEEKDAY(A1,2)<6 ),"Yes","No") "PJS" wrote: Hi everyone, I am looking for some help on the following. On business days (Yes), it would automatically input a value (400) under Daily Target Currently, I have a worksheet that looks like the following... Date Day Business Day Daily Target 12/1/2006 Friday Yes 400 12/2/2006 Saturday No 0 12/3/2006 Sunday No 0 12/4/2006 Monday Yes 400 etc.. Is it possible to automate excel to recognize 12/1/2006 as a business day, and assign the value of 400 under Daily Target. If it is possible, can excel recognize holidays as well? 12/25/2006 Monday (Holiday) ~ not a business day? I am open to any ideas, thanks, PJS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAn any one solve this problem of days months and years.its urgent plzzzzzzzzzzzz | Excel Discussion (Misc queries) | |||
No. of days split into periods | Excel Discussion (Misc queries) | |||
Calendar Days and Option Buttons | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Vacation Accrual Formula | Excel Worksheet Functions |