Excel vlookup if date is > n days

[lquinn]

hi all,

i've searched but have not been able to find an answer; i'm new to using excel for reporting (a change in job role), but not for coding

i have to report on performance statistics for my servers (~700); for each day of the month for each server i have both an average cpu load percentage and an average ram load percentage
(i need to identify low usage servers which will be candidates for either decommissioning, virtualization or consolidation onto existing servers)

i have set up pivot tables from the raw data on separate sheets using the built in 'top n' function and a summary sheet showing potential decom candidates (server names only) - as the performance statistics cover the entire fleet, new servers not yet fully in production will show up as false positives, so i need to cull these from the summary sheet to only show servers that have been in use for 90 days or longer

excel workbook setup;
separate sheet for source performance data - provided raw from a unix system
servername, date, cpu la %, ram la%
eg;
server1, 1 oct 08, 10, 20
server1, 2 oct 08, 9, 25
server2, 1 oct 08, 5, 4
server2, 2 oct 08, 5, 10

separate sheet for asset data - from our cmdb
servername, date record created
eg;
server1, 7 jun 07
server2, 1 sept 08

separate sheets for each of these pivot tables (tops and bottoms);
top 50 cpu load average
bottom 50 cpu load average
top 50 ram load average
bottom 50 ram load average

separate sheet for summary - read this as ready for boss consumption
this sheet identifies candidate server names only

the summary sheet is where i need some help to show servers that are older than 90 days if you can please?

thanks

[Ecniv]

=today() - 90

just change to a new column on your main data list :

=if({cell}>(today()-90),"New","90 days")

Then use that on your pivot table(s) as a filter or group...

[lquinn]

thanks ecniv

what i was looking for was to retain the raw data in the top/bottom 50 pivot tables, but filter content for the summary sheet...

so from the pivot tables showing all the data for 50 servers, i need to show the servernames on the summary sheet that are 90 days or older - the summary sheet could be the top 20 or 25

i.e., if the first record in the pivot table is older than 90 days, then show the servername on the summary sheet.
if the second record in the pivot table is younger than 90 days then just move on to the next record in the pivot table, and repeat testing for inclusion on the summary sheet

(pm me if you want the workbook)

thanks