N
Nath
Hi,
How do I calculated between two dates to return the value as 30, 60, 90 day.
How do I calculated between two dates to return the value as 30, 60, 90 day.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
smartin
Nath said:Hi,
How do I calculated between two dates to return the value as 30, 60, 90 day.
One way:
=CEILING(day2 - day1,30)
Substitute day2 and day1 with references to your actual dates.
N
Nath
Sorry, that did not work. I'm doing something work. What I'm looking for is:
Cell
A1 = 6/8/07
A2 = 12/11/08
I need it to return the value between the two date as 30,60,90,180,360, and
1yr+
Cell
A1 = 6/8/07
A2 = 12/11/08
I need it to return the value between the two date as 30,60,90,180,360, and
1yr+
S
smartin
Ah, that's a shade different than your original requirements, no?
Create a little table in an out-of-the-way place. I will use X1:Y6 to
demonstrate:
X Y
--------------
1E+31 1yr+
360 360
180 180
90 90
60 60
30 30
Now given your dates in A1 and A2 this will return the age bucket:
=INDEX(X1:Y6,MATCH(A2-A1,X1:X6,-1),2)
Create a little table in an out-of-the-way place. I will use X1:Y6 to
demonstrate:
X Y
--------------
1E+31 1yr+
360 360
180 180
90 90
60 60
30 30
Now given your dates in A1 and A2 this will return the age bucket:
=INDEX(X1:Y6,MATCH(A2-A1,X1:X6,-1),2)
J
Jacob Skaria
Another way..
=LOOKUP(A2-A1,{0,30,60,90,180,360,361},{0,30,60,90,180,360,"1 yr+"})
If this post helps click Yes
=LOOKUP(A2-A1,{0,30,60,90,180,360,361},{0,30,60,90,180,360,"1 yr+"})
If this post helps click Yes