Evaluate a date range and return a value

[StartingOut]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have workbook with about 500 rows using column A and B, column A has a date and column B has lust a product name. I need to evaluate the date. The evaluation is to check column A for the date and if it is between certain days, place a number value in column C<o></o>
<o> </o>
0 to 1 day old = 1 in column C<o></o>
2 to 5 days old = 2 in column C<o></o>
6 to 10 days old= 6 in column C<o></o>
11 to 20 days old = 11 in column C<o></o>
21 to 30 days old = 21 in column C<o></o>
31 to 60 days old = 31 in column C<o></o>
More than 60 days = 60 in column C<o></o>

 

[StartingOut]

I am slightly confued, on the code going in every row where ther is a date in column A, do I place the code in each row in column A? wont that get rid of my dates

 

[Desu Nota from Columbus]

Example:

You have 100 records. That means you have dates in A1-A100.

In C1 put: =LOOKUP(TODAY()-A1,{0,2,6,11,21,31,61},{1,2,6,11,21,31,60})

Drag this formula down to C100.

C1-C100 will now have the lookup formula

 

[Desu Nota from Columbus]

One last thing---if you are only dealing with dates before today (today changes everyday!) then you dont need to pay attention to the following.


If you are going to have dates that are in the future and you don't want an #N/A returned in column c, then use this formula instead in column C:

=IF((TODAY()-A1<0),"",IF(A1="","",LOOKUP(TODAY()-A1,{0,2,6,11,21,31,61},{1,2,6,11,21,31,60})))

 

[StartingOut]

I have an odd issue now, the formula works except ony for arriving at the 21 31 and 60 for all the others I get #VALUE or #N/A I have formatted all the dates the same

 

[Desu Nota from Columbus]

can you post your worksheet? Or type out the ones where the error occurs?

try retyping the dates where the formula returns an error to make sure they are Dates, not text.

If it isn't sensitive material I could take a look at the file through email.

 

[StartingOut]

its a date issue from what I can see, my date is MM/DD/YYYY so I set column dates the same format and cel ZZ10000 but still the #VALUE and #N/A

<TABLE style="WIDTH: 148pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=197 border=0><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 86pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=114 height=20>1/4/11 12:51 PM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 62pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=middle width=83>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/5/11 6:56 PM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5/7/11 8:38 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>31</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7/7/11 11:13 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6/7/11 3:17 PM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>21</TD></TR></TBODY></TABLE>

 

[StartingOut]

I dont seam to be able to attach a file

 

[Desu Nota from Columbus]

You don't need to use cell ZZ10000 at all. Delete it. Forget it.

<table border="0" cellpadding="0" cellspacing="0" width="197"><tbody><tr style="HEIGHT: 15pt" height="20"><td class="xl65" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 86pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align="right" height="20" width="114">1/4/11 12:51 PM</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 62pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="center" width="83">#VALUE!</td></tr><tr style="HEIGHT: 15pt" height="20"><td class="xl65" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align="right" height="20">3/5/11 6:56 PM</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">60</td></tr><tr style="HEIGHT: 15pt" height="20"><td class="xl65" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align="right" height="20">5/7/11 8:38 AM</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">31</td></tr><tr style="HEIGHT: 15pt" height="20"><td class="xl65" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align="right" height="20">7/7/11 11:13 AM</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="center">#N/A</td></tr><tr style="HEIGHT: 15pt" height="20"><td class="xl65" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align="right" height="20">6/7/11 3:17 PM</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">21</td></tr></tbody></table>

Use this formula instead: =IF((TODAY()-A1<0),"",IF(A1="","",LOOKUP(TODAY()-A1,{0,2,6,11,21,31,61},{1,2,6,11,21,31,60})))

I get the following

<table border="0" cellpadding="0" cellspacing="0" width="192"><colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt; width:48pt" align="right" height="17" width="64">1/4/2011</td> <td class="xl23" style="width:48pt" align="right" width="64">12:51</td> <td style="width:48pt" align="right" width="64">60</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" align="right" height="17">3/5/2011</td> <td class="xl23" align="right">6:56</td> <td align="right">60</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" align="right" height="17">5/7/2011</td> <td class="xl23" align="right">8:38</td> <td align="right">60</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" align="right" height="17">7/7/2011</td> <td class="xl23" align="right">11:13</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" align="right" height="17">6/7/2011</td> <td class="xl23" align="right">3:17</td> <td align="right">21</td> </tr> </tbody></table>

 

[Desu Nota from Columbus]

Is your data in column A a Date+Time ? Or is column A Date, and Column B Time?

If Column A has Date+Time use this formula instead and see if it makes a difference

=IF((NOW()-A1<0),"",IF(A1="","",LOOKUP(NOW()-A1,{0,2,6,11,21,31,61},{1,2,6,11,21,31,60})))

 

[StartingOut]

I didn't have any luck with the formulas, I sent you a private message