I'm trying to come up with a formula to calculate # of days passed from a range of cells... Example: Cells D125:AD125 contain 2 dates; 11/20/2012 (in D125) and 1/3/2013 (in M125). I want to automatically calculate the age of each and count them to display how many are <30 days, how many are 30-59 days, how many are 60-89 days, etc. (I do have a column for each age range; so column AF is <30, AG is 30-59, etc.) I've tried COUNT, COUNTIF, DAYS360, etc... Can't seem to get it right. Any suggestions would be greatly appreicated! Thanks!
Calculating Dates in a Range
- Thread starter vanguter
- Start date
may not the be best solution...but it works
This is assuming the following layout:
A1=first date
B1=second date
C1=If <30 Column
D1=If >=30 and <59 Column
E1:If >=60 and <89 Column
**Additional columns could be added as needed.
Formulas:
A2= none
B2= none
C2= =IF(B2-A2<30,B2-A2,"")
D2= =IF(AND(B2-A2>=30,B2-A2<60),B2-A2,"")
E2= =IF(AND(B2-A2>=60,B2-A2<90),B2-A2,"")
what this will do is it will look at the difference between the dates in b1 & a1.
-- if the Difference is <30 the value will show up in Col.C. (if it is not, nothing will be displayed)
-- If the difference is equal to or greater than 30 and less than 60, the value will show up in Col.D. (if it is not, nothing will be displayed)
-- the same happens in Col.E.
does this work for what you are needing?
This is assuming the following layout:
A1=first date
B1=second date
C1=If <30 Column
D1=If >=30 and <59 Column
E1:If >=60 and <89 Column
**Additional columns could be added as needed.
Formulas:
A2= none
B2= none
C2= =IF(B2-A2<30,B2-A2,"")
D2= =IF(AND(B2-A2>=30,B2-A2<60),B2-A2,"")
E2= =IF(AND(B2-A2>=60,B2-A2<90),B2-A2,"")
what this will do is it will look at the difference between the dates in b1 & a1.
-- if the Difference is <30 the value will show up in Col.C. (if it is not, nothing will be displayed)
-- If the difference is equal to or greater than 30 and less than 60, the value will show up in Col.D. (if it is not, nothing will be displayed)
-- the same happens in Col.E.
does this work for what you are needing?
Correction to my response to Luke... the cell was displaying as a date. Fixed that/changed to Number and it now shows 1 
What about the 30-60, 60-90, etc. Sorry, I'm pretty new at intricate formulas.
My other sheet is laid out as follows:
Cell A1: TODAY()
Cell A2: 11/20/2012
Cell A3: =$A$1-A2
Cell A4: =COUNTIF(A3,"<30")
Cell A5: =IF(AND(30<A3,A3<60),1,0)
Cell A6: =IF(AND(60<A3,A3<90),1,0)
Cell A7: =COUNTIF(A3,">90")
So that works great, but if I want to count multiple cells/dates, that's where my challenge is...
What about the 30-60, 60-90, etc. Sorry, I'm pretty new at intricate formulas. My other sheet is laid out as follows:
Cell A1: TODAY()
Cell A2: 11/20/2012
Cell A3: =$A$1-A2
Cell A4: =COUNTIF(A3,"<30")
Cell A5: =IF(AND(30<A3,A3<60),1,0)
Cell A6: =IF(AND(60<A3,A3<90),1,0)
Cell A7: =COUNTIF(A3,">90")
So that works great, but if I want to count multiple cells/dates, that's where my challenge is...
NARAYANK991
Excel Ninja
Hi Vanguter ,
Can you download the following file and see if this is what you want ?
I have copied Luke's formula.
http://speedy.sh/BXn3z/Vanguter-Example.xlsx
Narayan
Can you download the following file and see if this is what you want ?
I have copied Luke's formula.
http://speedy.sh/BXn3z/Vanguter-Example.xlsx
Narayan
The COLUMN function is there to serve as a counter, starting an increment count at 1. So, it first evaluates to
[pre]
[/pre]
So, each cell, in order, finds out how manys cells are older than 30 days, 60 days, 90 days, etc. The -SUM(...)[/code] bit is to subtract the portion that we've already counted (since we want 30-59, not 0-59).
[pre]
Code:
COLUMN(A1)*30
1*30
30
When you copy the cell to the right, it becomes
[code]COLUMN(B1)*30
2*30
60So, each cell, in order, finds out how manys cells are older than 30 days, 60 days, 90 days, etc. The -SUM(...)[/code] bit is to subtract the portion that we've already counted (since we want 30-59, not 0-59).
Sajan
Excel Ninja
Hi Luke,
Not sure if this matters to the OP, but if there are dates greater than TODAY(), the first value returned by the COUNTIF formula would include a count of those future dates.
I took a slightly different direction with the calculation, to allow the user to specify the actual age range of interest:
Assuming that the age is indicated as below (in cells A1:C5):
[pre]
[/pre]
and DateToCheck was set to "1/8/2013"
Cheers,
Sajan.
Edit: You can remove the N() from the above formula, since it is not needed.
Not sure if this matters to the OP, but if there are dates greater than TODAY(), the first value returned by the COUNTIF formula would include a count of those future dates.
I took a slightly different direction with the calculation, to allow the user to specify the actual age range of interest:
Assuming that the age is indicated as below (in cells A1:C5):
[pre]
Code:
Low High Count
0 30 3
31 60 1
61 90 1
91 600 2
The following formula in cell C2, would indicate the count corresponding to the age for dates between A2 and B2:
=INDEX(FREQUENCY(N(IF(ISNUMBER(DateData),(DateToCheck-DateData))), CHOOSE({1,2}, A2,B2)), 2)
entered with Ctrl + Shift + Enter
and copied down to C3:C5
Here "DataData" refers to the range of cells with dates, and "DateToCheck" refers to "=TODAY()", etc.
My sample test data was as follows:
12/15/2012 blank 12/28/2012 blank 2/5/2013 3/15/2013
10/15/2012 11/15/2012 2/2/2012 3/15/2012 blank 1/3/2013and DateToCheck was set to "1/8/2013"
Cheers,
Sajan.
Edit: You can remove the N() from the above formula, since it is not needed.
