Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula ... Minus Holidays?
Excel2003 ... I have an inherited spread sheet (ahhhhhhhh) which I am trying
to unravel. The following formulas work, but now I need to exclude Holidays: Cell F121 ... contains a Date (format = mm/dd) Range F12:F120 ... contains Formulas (format = Date = mm/dd) Range E12:E120 contains number of days (format = Number = 0.00) Cell F12 ... =IF($F$121="","",$F$121+SUM($E12:$E$120)) Cell F13 ... =IF($F$121="","",$F$121+SUM($E13:$E$120)) Cell F14 ... =IF($F$121="","",$F$121+SUM($E14:$E$120)) etc ... (Please note shrinking "E" Range) Issue ... I wish to write Formula to exclude the Holidays found in Range E12:E120??? Thanks for the guidance ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula ... Minus Holidays?
If you're dealing with working days (Mon-Fri) you might want to look at the
NETWORKDAYS function. Inputs are start date, end date, and holidays. Returns the number of days between dates minus holidays. Or, if you truly just want to give a StartDate, x number of days past that, minus any holiadys in between: =X-SUMIF(E12:E120=StartDate)+SUMIF(E12:E120StartDat e+X) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ken" wrote: Excel2003 ... I have an inherited spread sheet (ahhhhhhhh) which I am trying to unravel. The following formulas work, but now I need to exclude Holidays: Cell F121 ... contains a Date (format = mm/dd) Range F12:F120 ... contains Formulas (format = Date = mm/dd) Range E12:E120 contains number of days (format = Number = 0.00) Cell F12 ... =IF($F$121="","",$F$121+SUM($E12:$E$120)) Cell F13 ... =IF($F$121="","",$F$121+SUM($E13:$E$120)) Cell F14 ... =IF($F$121="","",$F$121+SUM($E14:$E$120)) etc ... (Please note shrinking "E" Range) Issue ... I wish to write Formula to exclude the Holidays found in Range E12:E120??? Thanks for the guidance ... Kha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the number of working days minus holidays (Canadian) | New Users to Excel | |||
Formula to skip holidays | Excel Worksheet Functions | |||
Calculating business days minus holidays | Excel Worksheet Functions | |||
how do I calculate the time service minus holidays? | Excel Worksheet Functions | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) |