Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Dates | Excel Worksheet Functions | |||
CALCULATING DATES | New Users to Excel | |||
calculating due dates | Excel Discussion (Misc queries) | |||
Calculating dates | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |