Copy row if value within date range diff wkbk Closed

Question

Hello, 

I have a workbook that has current and previous customers listed. I want to create a new workbook with one worksheet that contains a list of the customer information for customers whose contracts will be up within 60-90 days. I want to create a couple of other worksheets with customer info for customers that have cancelled their contracts within the last 30 days. 

In the original workbook with the customer information I have a column (T) that lists the contract end dates. If that is within the next 60-90 days I want to copy the whole row and paste it into a new workbook in the "Expiring 60-90" worksheet.

Is there a way to do this? Please help?

Thanks,

Meg

RayH · Answer

A possible solution is:
In this example the formula is any cell in row 2, T2 contains the contract end date:
    =IF(T2>NOW()+60,IF(T2<=NOW()+90,"Expiring",""),"")
copy this formula down all rows.
Any contracts that expire with the 60-90 days will show as 'Expiring'.
Now, once this is down, apply the autofilter and filter for those rows that show 'Expiring' in column T.
Then copy and paste the rows shown into a new sheet.

Or create a pivot table and filter it that way. This can be copy and paste-special values into a new sheet.

Copy row if value within date range diff wkbk

1 response

Similar discussions

Subscribe To Our Newsletter!