challenging formula(for me), counting days between dates for multipleyears

[TG]

Hi all

Been awhile since I've used Excel a lot so please bear with me..

I need to create a formula that will calculate the number of days passed
that fall between two dates, subtracted from a delivery date. For
example, we have a product that if it is harvested between 15-may and
15-nov in any given year, any days the product sits between those dates
are counted as "drying" days. The delivery date will then be used to
calculate if the product is "dry" or "green" when delivered, based on a
set number of days.

So, if a product was harvested on 15-oct-05, and delievered on 15-feb-06
, I need the formula to count the days passed from 15-oct-05 and
15-nov-05, if this same product was delivered on 15-jun-06 then I'd need
the number of days passed between 15-oct-05 to 15-nov-05 amd 15-may-06
to the actual delivery date, in this case 15-jun-06. It is possible as
well for the time the product sat to span several years, but in this
case I am dealing with 2003 to present.

I am thinking a long(and ugly) formula with many nested IF,AND,OR
functions in it may work, but like I said, it has been years since I've
worked extensively with Excel so perhaps someone here could show me a
better way and/or there are new worksheet functions I dont know about
that could simplfy this.


Any help greatly appreciated!!


TIA


TG

 

[Karthik]

Sorry i am not clear of your query but this formula will give you the
duration between 2 dates

in A1 enter the first date, B1 enter the second date and in C1
=datedif(A1,B1,"d")
d represents days
m represents months
y represents years

Hope this is on similar lines

 

[Pete_UK]

If you just want elapse days between two dates, all you need to do is
subtract one from the other and format the cell as a number with 0 dp.

Hope this helps.

Pete

 

[TG]

Sorry i am not clear of your query but this formula will give you the
duration between 2 dates

in A1 enter the first date, B1 enter the second date and in C1
=datedif(A1,B1,"d")
d represents days
m represents months
y represents years

Hope this is on similar lines

Thanks,

Maybe I wasnt clear enough, here is what I want to do:

We harvest a product, when the product is harvested it is considered
"green" until a set number of days passes, and these days have to be
between the 15th of May and the 15th of Nov in any given year. Any days
the product sits that aren't between those dates are not considered
"drying" days.

So, I have a sheet with several columns, included are harvested date and
delivered date, I need to insert a column that calculates the "drying
days" the product sat from the time it was harvested until the time it
was delivered. This is where it gets tricky..

For example, if the product was harvested on Nov 10th 2005, and
delivered on Dec 15 2005, there would be 5 drying days counted. If the
product was delivered on Jan 31st 2006 it is still only 5 drying days,
however if the product was delievered AFTER May 15, 2006 then any days
falling after May 15th 2006 also count as "drying days" in addition to
the 5 in 2005, so if the delivery date was May 20 2006 the number of
drying days I need the formula to calculate is 10.

It is also possible that the product may not be delivered until 2007 for
example, so in a nutshell I need to calculate all days the product has
sat between the dates 15-may and 15-nov of any given year, and it could
span 2 or more years.

I have done a formula using an IF formula with a nested AND in it that
works, but the delivered date needs to be in the same year, which is
often not the case.

Here is an example:

Harvested Delivered Drying days
10-05-2003 20-05-2003 5
10-05-2003 20-12-2003 184
10-05-2003 20-05-2004 189

I entered the Drying days numbers in the above example, but I want a
formula to do this for me.

I hope that makes it clearer!

TG

 

[Guest]

TG,

OK...took me a while, but see how we go!
Long formula!

=($F$10-$E$10)*(YEAR(F5)-YEAR(E5))+MAX(0,F5-DATE(YEAR(F5),MONTH($E$10),DAY($E$10)))-MAX(0,F5-DATE(YEAR(F5),MONTH($F$10),DAY($F$10)))

This assumes that:
Harvest date is in cell E5
Delivery date is in cell F5
Low date (15-May) is in cell E10
High date (15-Nov) is in cell F10

I have also entered the high date and low dates as 15-May-2000 and
15-Nov-2000 for the formula purpose. The year is really irrelevant however,
but simply a base year for calculations.

Anway, this works for all the examples you gave.

Any problems, contact me on:
(e-mail address removed)

Rich

 

[Guest]

Your post kept me from having to ask the question Thank you