averaging a date calculation

[David]

I am trying to get some averages of days between two dates in our production cycle. The problem that I run into is that if a job is active, and I have only put in the start date and no finish date, in the column that is set up to count the days between these two dates often results in something like -24000 days. I need to either tell the day calculation not to return a number less than zero or tell an average formula to ignore anything that is less than zero. I hope this makes sense.

Thanks,
David

 

[Peo Sjoblom]

=AVERAGE(IF(A1:A20>0,A1:A20))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

I am trying to get some averages of days between two dates in our

production cycle. The problem that I run into is that if a job is active,
and I have only put in the start date and no finish date, in the column that
is set up to count the days between these two dates often results in
something like -24000 days. I need to either tell the day calculation not to
return a number less than zero or tell an average formula to ignore anything
that is less than zero. I hope this makes sense.

 

[Aladin Akyurek]

=IF(FinishDate,FinishDate-StartDate,"")

will not thwart your AVERAGE formula.

I am trying to get some averages of days between two dates in our

production cycle. The problem that I run into is that if a job is active,
and I have only put in the start date and no finish date, in the column that
is set up to count the days between these two dates often results in
something like -24000 days. I need to either tell the day calculation not to
return a number less than zero or tell an average formula to ignore anything
that is less than zero. I hope this makes sense.

 

[2rrs]

*Hi,
I'm trying to calculate the difference between two dates
in months; the DATEDIF formula works fine. However, the
user wants the month calculation to change depending on
how many days into the month he is. A whole month does not
occur until the end of the month; he wants the whole month
to be counted from any date greater than the 14th of the
month. This would be the criteria for either the start
date or the end date or both. I've tried several formulas
with mild success which is getting me nowhere. Any
solutions would be wonderfully appreciated. Thanks. *

Hi Eva,

try this:
=DATEDIF(A128,A129,"m")+(DATEDIF(A128,A129,"md")>=14)

2rr