calculating dates to now()
 

I've got a date field column (say its in column A and starts at A1 and goes
through A150) and alot of dates are in it. How can I calculate each date
field in a separate column (say column C1 through c150 for each) and it gives
the total number of days from each date until now? Then total all them up
and come up with an average over the entire date field?

I hope this is enough information for someone to help! Thanks!!
--
Todd

calculating dates to now()
 

You can do that with a single array formula** :

=AVERAGE(TODAY()-A1:A150)

If the date field might contain empty cells then use this array formula** :

=AVERAGE(IF(ISNUMBER(A1:A150),TODAY()-A1:A150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Todd" wrote in message
...
I've got a date field column (say its in column A and starts at A1 and
goes
through A150) and alot of dates are in it. How can I calculate each date
field in a separate column (say column C1 through c150 for each) and it
gives
the total number of days from each date until now? Then total all them up
and come up with an average over the entire date field?

I hope this is enough information for someone to help! Thanks!!
--
Todd

calculating dates to now()
 

All of the date fields do have dates in them so I used the first formula but
when I entered it and followed the ctl,shift,enter process the result was
12/30/2007. I don't believe this is my average date. What could be wrong.
Below is the formula I entered:

=AVERAGE(TODAY()-C1:C122)
--
Todd


"T. Valko" wrote:

You can do that with a single array formula** :

=AVERAGE(TODAY()-A1:A150)

If the date field might contain empty cells then use this array formula** :

=AVERAGE(IF(ISNUMBER(A1:A150),TODAY()-A1:A150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Todd" wrote in message
...
I've got a date field column (say its in column A and starts at A1 and
goes
through A150) and alot of dates are in it. How can I calculate each date
field in a separate column (say column C1 through c150 for each) and it
gives
the total number of days from each date until now? Then total all them up
and come up with an average over the entire date field?

I hope this is enough information for someone to help! Thanks!!
--
Todd

calculating dates to now()
 

Format the formula cell as General.

--
Biff
Microsoft Excel MVP


"Todd" wrote in message
...
All of the date fields do have dates in them so I used the first formula
but
when I entered it and followed the ctl,shift,enter process the result was
12/30/2007. I don't believe this is my average date. What could be
wrong.
Below is the formula I entered:

=AVERAGE(TODAY()-C1:C122)
--
Todd


"T. Valko" wrote:

You can do that with a single array formula** :

=AVERAGE(TODAY()-A1:A150)

If the date field might contain empty cells then use this array formula**
:

=AVERAGE(IF(ISNUMBER(A1:A150),TODAY()-A1:A150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Todd" wrote in message
...
I've got a date field column (say its in column A and starts at A1 and
goes
through A150) and alot of dates are in it. How can I calculate each
date
field in a separate column (say column C1 through c150 for each) and it
gives
the total number of days from each date until now? Then total all them
up
and come up with an average over the entire date field?

I hope this is enough information for someone to help! Thanks!!
--
Todd