Excel List help

AndrewD04

New Member
Joined
Aug 24, 2017
Messages
26
Hi Guys,

I am working on a piece of work where i ahve a list of names and dates next to them.

What i would liek to do is excel to scan all the dates and any date less than today to return the name to the left of the date however i only want it to build a list of names that have expired and not a full list with blanks.

Sample Data
Kerry 30/07/2019
Steve 16/05/2019
Clare 15/09/2019
John 14/08/2015

Results i would like
Kerry
Steve
John

Thanks
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hey,

Is this something you're after?

AB
1Sample DataResults
2Kerry 30/07/2019Kerry
3Steve 16/05/2019Steve
4Clare 15/09/2019
5John 14/08/2015John

<tbody>
</tbody>


Where B2 has the formula (dragged down):

<today(),trim(left(a2,len(a2)-10)),""),"")
Code:
IFERROR(IF(EDATE(RIGHT(A2,10),0)<=TODAY(),TRIM(LEFT(A2,LEN(A2)-10)),""),"")
The main assumption here is that the string in column A always ends with a date in dd/mm/yyyy format (i.e. exactly 10 characters)</today(),trim(left(a2,len(a2)-10)),""),"")
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
Id have thought that the names are in one column and the dates another. If so try this array formula that requires CTRL-SHIFT-ENTER

=IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4 < TODAY(),ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($A$1:A1))),"")<today(),row($b$1:$b$4)-row($b$1)+1),rows($a$1:a1))),"")< html=""></today(),row($b$1:$b$4)-row($b$1)+1),rows($a$1:a1))),"")<>
 
Last edited:

Forum statistics

Threads
1,077,822
Messages
5,336,566
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top