Expiry date help

[Lemmo86]

Hi,

I wonder if anyone can help,

I would like to extract expiry dates and dates that are coming up in the next 4 month to 2 different worksheets.

I have conditional formatting to show what’s expired but want to pull the name and course that has and is expiring to the two worksheets.

Names are in column an and the courses are in columns d to p with the date against everyone names under the correct course columns. I created a table and called it table 2 if that helps.

Please help.

 

[Joe4]

Welcome to the Board!

Since you are using Excel 365, I think this should be pretty easy to do using the new FILTER function (different from the FILTER menu items).
See: FILTER function - Microsoft Support

 

[Lemmo86]

Welcome to the Board!

Since you are using Excel 365, I think this should be pretty easy to do using the new FILTER function (different from the FILTER menu items).
See: FILTER function - Microsoft Support

Silly question, how do I get it to look between the two dates. So the first date is todays date and then the second date is in 4 months time?

 

[Joe4]

Like this:

Sample data in range A1:B7

Formula in cell E1 to return current date:

=TODAY()

Formula in cell F1 to return date 4 months from today:

=EDATE(TODAY(),4)

Formula in cell E4 to return desired results:

=FILTER(A2:B7,(B2:B7>=E1)*(B2:B7<=F1),"")

 

[Joe4]

Note that you do not need cells E1 and F1 at all. I just broke it out to make it more obvious what it is doing.
You can put it all together in one formula like this:

=FILTER(A2:B7,(B2:B7>=TODAY())*(B2:B7<=EDATE(TODAY(),4)),"")

 

[Lemmo86]

That’s great thank you. I think I’m struggling because the names are all running down column A and then the courses run in Row 2 from column D To P with the dates obviously running down each column to the appropriate name It just returns value. Some cells are blank as they haven’t attended that course. I tried to upload an example of my sheet but it won’t let me :/

 

[Joe4]

Yes, I think I would definitely need to see your data.

You can post it using a tool MrExcel provides called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you are unable to do that, at least post an image of your data.

 

[Lemmo86]

This is an example. I can’t send the actual one due to data protection.

 

[Joe4]

That is a very difficult format to work with, as you have to check every single date column!

Can you show us exactly what you want the result to look like?
With all those different columns, I am not sure how you want the result formatted.

 

[Lemmo86]

I just want three columns to come back to a separate worksheet in the same workbook. Like a summary page it will return the name, course that will expire and the date it will expire. I can change the table to make it easier to extract if needs be.