If function across multiple columns with empty cells

[Bob_ipc]

I could really use some help on this one. I currently have a formula that goes like this. =IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7<TODAY()+90,"Expires Soon",""),IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7,"","") the idea is to have "Expires Soon" notification pop up when the expire date is within 90 days. Some columns contain empty or blank cells. how can I make this work? Please Help.

Thank you.

 

[RasGhul]

Hi Bob,

What are you trying to achieve and do you have some sample data we can see to help you?

 

[Peter_SSs]

I currently have a formula that goes like this. =IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7<TODAY()+90,"Expires Soon",""),IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7,"","")

Ignoring the blank cell issue for a moment, that isn't a valid Excel formula. Do you have one this is working if there are no blank cells?

 

[Bob_ipc]

Hi Peter_SSs, thank you for the reply. I can use =if(h7< today()+90,"Expires Soon","") but how do I get it to tell me when 90 days is close in any of the other cells? and some cells are blank.< today()+90,"expires soon","")="" and="" it="" works,="" but="" how="" do="" i="" get="" to="" tell="" me="" if="" any="" of="" the="" cells="" listed="" come="" close="" 90="" days?="" also="" some="" cell="" contain="" blanks="" as="" well.

Thank you.

 

[Peter_SSs]

The forum software thinks that a < sign followed immediately by a letter is some HTML code that it should act on. Add a space between to stop your formula or text being cut off, as I have done in your last post.

Do the columns that you are interested in have a heading that is different to all the other headings? Or contain at least something that we could identify the relevant columns by?

You seemed to be indicating columns H, J, M, O, Q, .. which, except for the jump from J to M, the rest are every second column. Can you clarify? Or better still post a small set of sample data and expected results that we can copy to test with? My signature block below has a link with help on that.

 

[Bob_ipc]

So sorry it took so long for a response I had something come up, first thank you again for your response, below is an example of what I have, there are some training courses, each of them have and expire date. If the Name column is column "B" then I would like column "A" to display "Expires Soon" any time that any of the expire dates come close in that row for any of the employees.

I hope that helps.


[TABLE="width: 959"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Orientation
[/TD]
[TD="colspan: 2"]Annual IPC Values Orientation
[/TD]
[TD="colspan: 2"]First Aid
[/TD]
[TD]WHIMIS
[/TD]
[TD="colspan: 2"]TDG
[/TD]
[TD="colspan: 2"]Cargo Securement
[/TD]
[/TR]
[TR]
[TD]First Last
[/TD]
[TD]Employment Orientation
[/TD]
[TD]Issue Date
[/TD]
[TD]Expire Date
[/TD]
[TD]Date Issued
[/TD]
[TD]Expire Date
[/TD]
[TD]Date Issued
[/TD]
[TD]Date Issued
[/TD]
[TD]Expire Date
[/TD]
[TD]Date Issued
[/TD]
[TD]Expire Date
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]24-Jul-17
[/TD]
[TD]05-Jun-18
[/TD]
[TD]05-Jun-19
[/TD]
[TD]14-Dec-16
[/TD]
[TD]14-Dec-19
[/TD]
[TD]18-May-18
[/TD]
[TD]22-Oct-18
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Abe
[/TD]
[TD]
[/TD]
[TD]05-Jun-18
[/TD]
[TD]05-Jun-19
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Abe
[/TD]
[TD]
[/TD]
[TD]05-Jun-18
[/TD]
[TD]05-Jun-19
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]18-May-18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank You.

 

[Peter_SSs]

See if you can adapt something like this
A1 holds the current date by formula.
A3 is copied down

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	19/10/2018	Name	Orientation				Annual IPC Values Orientation		First Aid		WHIMIS	TDG		Cargo Securement
2		First Last	Employment Orientation				Issue Date	Expire Date	Date Issued	Expire Date	Date Issued	Date Issued	Expire Date	Date Issued	Expire Date
3	Expires Soon	Bob	24-Jul-17				5-Jun-18	5-Jun-19	14-Dec-16	14-Dec-19	18-May-18	22-Oct-18		15-Aug-18	22-Nov-18
4		Abe					5-Jun-18	5-Jun-19
5	Expires Soon	Abe					5-Jun-18	5-Jun-19		5/01/2019	18-May-18
Expiring Soon

Another option you might want to consider is to highlight the dates in question. This could be done instead of or as well as the previous suggestion.
To apply this select from the top left data cell (I used G3) down to the bottom right and apply the Condition Formatting formula rule as shown. The column/row references in the formula should relate to the active cell (G3 for me) or the heading row of that first cell column (eg where I used G$2).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	19/10/2018	Name	Orientation				Annual IPC Values Orientation		First Aid		WHIMIS	TDG		Cargo Securement
2		First Last	Employment Orientation				Issue Date	Expire Date	Date Issued	Expire Date	Date Issued	Date Issued	Expire Date	Date Issued	Expire Date
3	Expires Soon	Bob	24-Jul-17				5-Jun-18	5-Jun-19	14-Dec-16	14-Dec-19	18-May-18	22-Oct-18		15-Aug-18	22-Nov-18
4		Abe					5-Jun-18	5-Jun-19
5	Expires Soon	Abe					5-Jun-18	5-Jun-19		5/01/2019	18-May-18
Expiring Soon (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3	1. / Formula is =AND(G$2="Expire Date",G3>=$A$1,G3<=$A$1+90)	Abc

 

[Bob_ipc]

Sorry it took me such a long while to get back to you again, But this is awesome it works 100%!!!! Thanks so much!!

 

[Peter_SSs]

Sorry it took me such a long while to get back to you again, But this is awesome it works 100%!!!! Thanks so much!!

You're welcome. Thanks for letting us know.