Hi, How do I calculated between two dates to return the value as 30, 60, 90 day.
N Nath Jul 14, 2009 #1 Hi, How do I calculated between two dates to return the value as 30, 60, 90 day.
S smartin Jul 14, 2009 #2 Nath said: Hi, How do I calculated between two dates to return the value as 30, 60, 90 day. Click to expand... One way: =CEILING(day2 - day1,30) Substitute day2 and day1 with references to your actual dates.
Nath said: Hi, How do I calculated between two dates to return the value as 30, 60, 90 day. Click to expand... One way: =CEILING(day2 - day1,30) Substitute day2 and day1 with references to your actual dates.
N Nath Jul 14, 2009 #3 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+
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+
S smartin Jul 14, 2009 #4 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)
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)
J Jacob Skaria Jul 14, 2009 #5 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
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
