Date Range Lookup Question

[htanveer]

Hi,

I have an Excel file which tracks expiration dates of all items with a support contract. I need to be able to lookup at anytime and quickly identify which items are coming up for renewal in the next 60 days. I can pull the concerned SNs in a separate sheet or color code them in the same file. Please let me know the best way to accomplish this.

Serial Numbers Contract End Date
ABCDEF2343 XX/XX/XXXX

 

[JoeMo]

Look into conditional formatting for that. It will allow you to fill the SN cells with a color of your choice which will happen automatically when the associated dates for renewal are less than or equal to 60 days from today. The color will disappear when the renewal date is earlier than today's date.

 

[Trebor76]

You can also filter by contract end date for the desired range. This filtered (visible) range can easily be copied to another tab.

 

[Twollaston]

Hi,

I have an Excel file which tracks expiration dates of all items with a support contract. I need to be able to lookup at anytime and quickly identify which items are coming up for renewal in the next 60 days. I can pull the concerned SNs in a separate sheet or color code them in the same file. Please let me know the best way to accomplish this.

Serial Numbers Contract End Date
ABCDEF2343 XX/XX/XXXX

You can use conditional formatting. Highlight the cells you want to conditionally format, and then set a new conditional formatting rule that looks like this
=$L1-TODAY()<=60
But instead of L you want to put the column with the contract end dates. (You can do it for the whole dataset if you want the entire row highlighted)