averaging a date calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=AVERAGE(IF(A1:A20>0,A1:A20))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom


David said:
I am trying to get some averages of days between two dates in our
Click to expand...
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.
 
=IF(FinishDate,FinishDate-StartDate,"")

will not thwart your AVERAGE formula.

David said:
I am trying to get some averages of days between two dates in our
Click to expand...
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.
 
Eva said:
*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. *
Click to expand...

Hi Eva,

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

2rr
 
Back
Top