Formula ... Minus Holidays?

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

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
 
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:E120>StartDate+X)
 
Back
Top