Formula to Extract Address Based on Today's Date

[Justinian]

I am trying to use the FILTER function on a sample data set below and I need to extract just the address in column A based on dates in columns J through AF. The problem is that the date could appear in any column J through AF and it appears in a cell that looks like this: "Scheduled: 07/06/23" so if I use =IFERROR(MID(B2,FIND("/",B2)-2,8),0) to extract just the date, the FILTER function will not recognize the result of 07/06/23.

Here is a sample:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Address	Floorplan code	Floorplan name	Unit status	Move out date	Move in date	Complete by date	Date made ready	Requests done	1st Insp	Pest	Strip	Upgrade Start	Upgrade Finish	Counter tops	Install AC	Mech	Kitchen Floor	Appliance	Dishwasher	Paint	Clean	Hardwood Floor	Shampoo	Carpet	Tub Reglaze	First Walk	Corrections	Final Corrections	Final PTU	PLB Final	Rekey exterior door locks
2	09-0919	21 G 2 KU	021 Garden 2Bd Kit Upgd	Vacant Leased	6/13/2022	08/01/23	07/31/23		22	Completed: 06/14/22	Completed: 07/27/22 Scheduled: 06/14/22 TREAT VACANT UNIT - DUST OUTLETS	Completed: 06/14/22	Completed: 06/14/22	Completed: 06/14/22	Completed: 06/14/22	Completed: 06/14/22 C/AIR	Completed: 06/21/22 Scheduled: 06/20/22 LEVEL 1 comp by a/m 06/20/22	Completed: 06/14/22	Completed: 06/14/22	Completed: 06/14/22	Completed: 06/28/22 Scheduled: 06/21/22 G2 - KIT UPGRADE - FULL PAINT	Completed: 06/27/22 Scheduled: 06/27/22 Complete 06/27 TA	Completed: 06/27/22 Scheduled: 06/24/22 Complete 06/24 Santos	Completed: 06/14/22	Completed: 07/07/22 Scheduled: 06/23/22 HW/HW - STAIRS	Completed: 06/14/22	Completed: 06/28/22 Scheduled: 06/27/22 Complete 06/28 HB	Completed: 07/01/22 Scheduled: 06/28/22 comp by carmelo 06/30/22	Completed: 07/01/22 comp by carmelo 06/30/22	Completed: 07/27/22	6/29: NB Walked - Corrections given to OC. 11.04 down for mortar repair	Completed: 07/27/22
3	10-1055	17 G 1U KU	017 Garden 1Bd Upper Kit Upgd	Vacant Not Leased	6/24/2023		07/08/23		8	Completed: 06/26/23	Scheduled: 06/26/23 TREAT VACANT UNIT - DUST OUTLETS	Completed: 06/26/23	Completed: 06/26/23	Completed: 06/26/23	R/R KITCHEN COUNTER (FORMICA)	Completed: 06/26/23 1 AC	Scheduled: 06/30/23 level 1	Completed: 06/26/23	R/R STOVE, FRDIGE (STAINLESS STEEL)	R/R DISHWASHER (STAINLESS STEEL)	Scheduled: 07/03/23	Scheduled: 07/06/23	Scheduled: 07/07/23	Completed: 06/26/23	COMPLETE NO ENTRY	Completed: 06/26/23
4	11-1120	14 G 1LKU	014 Garden 1Bd Lower Kit Upgd	Vacant Not Leased	12/18/2021		07/31/23		2	Completed: 04/03/23 FIRE UNIT - DNR	TREAT VACANT UNIT - DUST OUTLETS	Completed: 04/03/23	Build out unit				'07/29/23				'07/29/23	'07/29/23	'07/29/23
5	12-1217	17 G 1U KU	017 Garden 1Bd Upper Kit Upgd	Vacant Leased	6/23/2023	07/15/23	07/07/23		7	Completed: 06/26/23	Scheduled: 06/26/23 TREAT VACANT UNIT - DUST OUTLETS	Completed: 06/26/23	Completed: 06/26/23	Completed: 06/26/23	CUTOUT COUNTERTOP FOR STOVE INSTALL	Completed: 06/26/23 1 AC	Scheduled: 06/29/23 LEVEL 1 - R/R COOKTOP & BUILDBOUT CABINETS	Completed: 06/26/23	R/R STOVE, FRIDGE, MICROWAVE (WHITE)	R/R DISHWASHER (WHITE)	Scheduled: 07/03/23 G1 - UPGRADE - FULL PAINT ***CARPET WILL BE REPLACED***	Scheduled: 07/06/23	Scheduled: 07/07/23	Completed: 06/26/23	COMPLETE NO ENTRY	REGLAZE TUB	Scheduled: 07/06/23	Scheduled: 07/07/23
6	12-1253	14 G 1LKU	014 Garden 1Bd Lower Kit Upgd	Vacant Leased	6/21/2023	07/08/23	07/05/23		10	Completed: 06/22/23	Scheduled: 06/22/23 TREAT VACANT UNIT - DUST OUTLETS	Completed: 06/22/23	Completed: 06/22/23	Completed: 06/22/23	Completed: 06/22/23	Completed: 06/22/23 0/AC	Scheduled: 06/29/23	R/R KITCHEN FLOOR	R/R WHITE REFRIGERATOR	Completed: 06/22/23	Completed: 06/28/23 Scheduled: 06/28/23	Scheduled: 07/03/23	Scheduled: 06/30/23	Completed: 06/22/23	Completed: 06/22/23 HW/HW		Scheduled: 07/03/23	Scheduled: 07/04/23
7	14-1438	19 G 2B	019 Garden 2Bd	Vacant Not Leased	10/31/2022		07/31/23		19	Completed: 11/01/22	Scheduled: 11/01/22 TREAT VACANT UNIT - DUST OUTLETS	Completed: 11/01/22	Completed: 11/01/22	Completed: 11/01/22	Completed: 11/01/22	Completed: 11/01/22 C/AIR	Completed: 11/04/22 Scheduled: 11/03/22 Complete 11/3 R/J LEVEL 1	Completed: 11/01/22	Completed: 11/09/22 '11/08/22 R/R - REFRIGERATOR (WHITE)	Completed: 11/09/22 '11/08/22 R/R - DISHWASHER (WHITE)	Completed: 11/07/22 Scheduled: 11/07/22 G2 - CLASSIC - FULL PAINT ***CARPET WILL BE REPLACED***	Completed: 11/11/22 Scheduled: 11/10/22 COMP BY TEAM A 11/10/22	Completed: 11/10/22 Scheduled: 11/09/22 comp by santo 11/09/22	Completed: 11/01/22	Completed: 11/09/22 Scheduled: 11/07/22 STAIRS ONLY	Completed: 11/01/22	Completed: 11/15/22 Scheduled: 11/16/22 comp gy sam 11/15/22	Completed: 11/16/22 Scheduled: 11/16/22 comp by arturo 11/15/22			Completed: 12/06/22 12/5: NB walked - Corrections given to ABM & Painters.
8	15-1514	20 G 2 Upg	020 Garden 2Bd Upgrade	Vacant Leased	6/29/2023	08/02/23	07/13/23		0								Scheduled: 07/03/23				Scheduled: 07/05/23	'07/15/23	Scheduled: 07/07/23
Sheet2

 

[DanteAmor]

(MID(B2,FIND("/",B2)-2,8),0)

The MID function returns a text, so if you are going to compare it against a date, then you must convert that text to a date and thus perform the comparison.

------

Completed: 07/27/22 Scheduled: 06/14/22 TREAT VACANT UNIT - DUST OUTLETS

But I have a question, because you have texts with 2 dates, so if you search with Find it will extract the first one and I don't know if it is the correct one.

-----

How about:

07/06/23

If you are going to search for that date, in the cells you have texts, one option is to search for the date as text:

=IFERROR(FILTER(A2:A8,BYROW(J2:AF8,LAMBDA(br,SUM(--ISNUMBER(SEARCH("07/06/23",br)))))),"Not Exists")

 

[Justinian]

The MID function returns a text, so if you are going to compare it against a date, then you must convert that text to a date and thus perform the comparison.

------

But I have a question, because you have texts with 2 dates, so if you search with Find it will extract the first one and I don't know if it is the correct one.

-----

How about:

If you are going to search for that date, in the cells you have texts, one option is to search for the date as text:

=IFERROR(FILTER(A2:A8,BYROW(J2:AF8,LAMBDA(br,SUM(--ISNUMBER(SEARCH("07/06/23",br)))))),"Not Exists")

Thank you for your reply.

I am just looking to extract the first date if there are cells where more than one date appears.

The idea is to use FILTER so I can display any addresses that have only today's date in any of the rows. The plan here is for my team to open this spreadsheet in the morning and only see the addresses that are scheduled for the current day's date so they can know what is in progress for the day.

 

[DanteAmor]

Entonces solamente debemos poner la fecha de hoy en la misma fórmula para buscarla. Pero con el formato mm/dd/yy

=IFERROR(FILTER(A2:A8,BYROW(J2:AF8,LAMBDA(br,SUM(--ISNUMBER(SEARCH(TEXT(TODAY(),"mm/dd/yy"),br)))))),"Not Exists")

 

[Justinian]

Estoy feliz de poder leer Español.

¡Gracias por su ayuda!

 

[DanteAmor]

Entonces solamente debemos poner la fecha de hoy en la misma fórmula para buscarla. Pero con el formato mm/dd/yy

So we just have to put today's date in the same formula to find it. But with the format mm/dd/yy

Estoy feliz de poder leer Español.

¡Gracias por su ayuda!

Sorry about that, sometimes I forget to do the translation, I hope I don't get scolded by the forum moderators.

I appreciate your kindness to accept the text in Spanish and above all that the solution works for you.