Formula for calculating # of days between dates minus a particular date if present

  • Thread starter Thread starter Jarod
  • Start date Start date
J

Jarod

I'm trying to create a formula that will calculate the number of days
between two dates automatically subtracting a day(s) from the equation
if a specified date(s) falls in between the two dates.

For example, I don't want 1/15/04 to register as a counted day. If I
enter the dates 1/1/04 in one cell and 1/12/04 in another cell then
it's a straight foward DATEDIF equation. But if I enter 1/1/04 and
1/28/04, then I want the formula to figure out the number of days
between 1/1 and 1/28 automatically subtracting 1/15 from the equation.
This gets trickier as I need some calucations that will be subtracting
multiple, non-contiguous days. (i.e. calculate the number of days
between 1/1/04 and 1/31/04 but not counting 1/5/04, 1/15/04, or
1/18/04).

I'm sure there's a way to do this using excel's date format, but I sure
can't figure it out. Can anyone help me on this.

Thanks!
 
My suggestion would be to simply subtract the dates and
subtract the count of dates to exclude:

=Date1-Date2-COUNT(C1:C5)

where the C1:C5 contains 1 to 5 dates needed to exclude
from your calculation.

HTH
Jason
Atlanta, GA
 
I'm trying to create a formula that will calculate the number of days
between two dates automatically subtracting a day(s) from the equation
if a specified date(s) falls in between the two dates.

For example, I don't want 1/15/04 to register as a counted day. If I
enter the dates 1/1/04 in one cell and 1/12/04 in another cell then
it's a straight foward DATEDIF equation. But if I enter 1/1/04 and
1/28/04, then I want the formula to figure out the number of days
between 1/1 and 1/28 automatically subtracting 1/15 from the equation.
This gets trickier as I need some calucations that will be subtracting
multiple, non-contiguous days. (i.e. calculate the number of days
between 1/1/04 and 1/31/04 but not counting 1/5/04, 1/15/04, or
1/18/04).

I'm sure there's a way to do this using excel's date format, but I sure
can't figure it out. Can anyone help me on this.

Thanks!
Click to expand...

The array formula:

=EndDate-StartDate-SUM((ExcludeDates>=StartDate)*(ExcludeDates<=EndDate))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

Put the "ExcludeDates" in a named range someplace.


--ron
 
Back
Top