Formatting Cell Based on content using Conditional Formatting

[itsrich]

I want the cell containing the following formula to change the fill color when the formula results in a date. =IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D3,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D3,'Where is production'!$D:$D,0),5))

The result of the formula is text or number

Currently, I am attempting to use conditional formatting to format the cell, but the text and numbers are formatted the same. I can figure out how to determine the difference between a number and text.

TY for your help

 

[etaf]

isnumber()
istext()

if its a date you want to fill the cell

A date is just a number - (number of days since 1900, but can also be text and so not a real date
maybe a sample here

check the source if its a real date by changing the format to general and see if you get a number

 

[itsrich]

The Orange row is the formatted row. See below for a picture of the conditional formatting settings.
Once I figure out how to do this for the column I am working on, I will use it on the other columns.

When I clear all formatting I get Excel's number system for the date and text for this column.

I think there is knowledge I need to master about how the "state" of cells changes. I have not found that nugget of info yet. I think once I have a better handle on this, my spreadsheets will move to the next level. I am so thankful for Mr. Excel. This is the only place I have found to help me learn!

2023.02.13 FAB Tracking & Reporting Sheet.xlsx
	I	J	K	L	M	N
23	16-Dec	5-Jan	5-Jan	5-Jan	18-Jan	5-Jan
24	19-Dec	5-Jan	5-Jan	5-Jan	18-Jan	5-Jan
25	11-Nov	15-Nov	16-Nov	17-Nov	5-Dec	5-Dec
26	17-Nov	17-Nov	5-Dec	12-Dec	16-Dec	12-Dec
27	22-Nov	5-Dec	5-Dec	12-Dec	X	12-Dec
28	5-Dec	12-Dec	5-Jan	5-Jan	18-Jan	5-Jan
29	6-Dec	12-Dec	16-Dec	16-Dec	16-Dec	16-Dec
30	6-Dec	12-Dec	16-Dec	16-Dec	16-Dec	16-Dec
31	13-Dec	5-Jan	5-Jan	5-Jan	18-Jan	5-Jan
32	12-Dec	12-Dec	5-Jan	5-Jan	18-Jan	5-Jan
33	29-Dec	5-Jan	18-Jan	18-Jan	30-Jan	18-Jan
34	29-Dec	5-Jan	18-Jan	18-Jan	30-Jan	18-Jan
35	4-Jan	18-Jan	18-Jan	18-Jan	24-Jan	19-Jan
36	14-Dec	5-Jan	5-Jan	5-Jan	19-Jan	5-Jan
37	20-Dec	5-Jan	18-Jan	18-Jan	18-Jan	18-Jan
38	20-Dec	5-Jan	18-Jan	18-Jan	19-Jan	18-Jan
39	20-Dec	5-Jan	18-Jan	18-Jan	19-Jan	18-Jan
40	2-Feb	5-Jan	19-Jan	19-Jan	WIP	19-Jan
41	3-Jan	18-Jan	19-Jan	19-Jan	WIP	19-Jan
42	5-Dec	12-Dec	5-Jan	5-Jan	18-Jan	5-Jan
43	20-Dec	5-Jan	18-Jan	18-Jan	18-Jan	18-Jan
44	21-Dec	5-Jan	18-Jan	18-Jan	WIP	18-Jan
45	22-Dec	5-Jan	18-Jan	18-Jan	WIP	18-Jan
46	27-Dec	5-Jan	18-Jan	18-Jan	25-Jan	18-Jan
47	27-Dec	5-Jan	18-Jan	18-Jan	25-Jan	18-Jan
The Time Line CURRENT

Cell Formulas
Range	Formula
I23:I47	I23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),1)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),1))
J23:J47	J23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),2)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),2))
K23:K47	K23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),3)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),3))
L23:L47	L23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),4)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),4))
M23:M47	M23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5))
N23:N47	N23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),6)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),6))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M3:M47	Expression	=ISNUMBER($M$4:$M$47)	text	NO

 

[etaf]

because of reference to other sheets, I cannot copy in

if you elect a range in column N

now change the format to a number

if nothing changes , then all those values are text

something like

Book1
	E
1	44896
2	44897
3	44898
4	44899
5	44900
6	44901
7	44902
8	44903
9	44904
10	12-jan
11	13-dec
Sheet1

 

[itsrich]

This is the same range with all formatting stripped. The Excel 5-digit date & text ("x" and "WIP"), are sourced from other sheets.

2023.02.13 FAB Tracking & Reporting Sheet.xlsx
	I	J	K	L	M	N
23	44911	44931	44931	44931	44944	44931
24	44914	44931	44931	44931	44944	44931
25	44876	44880	44881	44882	44900	44900
26	44882	44882	44900	44907	44911	44907
27	44887	44900	44900	44907	X	44907
28	44900	44907	44931	44931	44944	44931
29	44901	44907	44911	44911	44911	44911
30	44901	44907	44911	44911	44911	44911
31	44908	44931	44931	44931	44944	44931
32	44907	44907	44931	44931	44944	44931
33	44924	44931	44944	44944	44956	44944
34	44924	44931	44944	44944	44956	44944
35	44930	44944	44944	44944	44950	44945
36	44909	44931	44931	44931	44945	44931
37	44915	44931	44944	44944	44944	44944
38	44915	44931	44944	44944	44945	44944
39	44915	44931	44944	44944	44945	44944
40	44959	44931	44945	44945	WIP	44945
41	44929	44944	44945	44945	WIP	44945
42	44900	44907	44931	44931	44944	44931
43	44915	44931	44944	44944	44944	44944
44	44916	44931	44944	44944	WIP	44944
45	44917	44931	44944	44944	WIP	44944
46	44922	44931	44944	44944	44951	44944
47	44922	44931	44944	44944	44951	44944
The Time Line CURRENT

Cell Formulas
Range	Formula
I23:I47	I23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),1)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),1))
J23:J47	J23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),2)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),2))
K23:K47	K23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),3)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),3))
L23:L47	L23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),4)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),4))
M23:M47	M23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5))
N23:N47	N23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),6)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),6))

 

[etaf]

ok, so they are dates

how about this using isnumber() and istext()

The column B is just to show the dates formatted as a number

again your example does not copy -

Book2
	A	B
1
2	1-Feb	44958
3	2-Feb	44959
4	3-Feb	44960
5	4-Feb	44961
6	5-Feb	44962
7	6-Feb	44963
8	7-Feb	44964
9	x	x
10	9-Feb	44966
11	wip	wip
12	11-Feb	44968
13	s	s
14	13-Feb	44970
15	14-Feb	44971
16	15-Feb	44972
Sheet2

Cell Formulas
Range	Formula
B2:B16	B2	=A2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A16	Expression	=ISTEXT(A2)	text	NO
A2:A16	Expression	=AND(ISNUMBER(A2),A2<>"")	text	NO

Otherwise

need to show what you want conditional formatted - or i'm not understanding - SORRY

 

[itsrich]

Hmmm. The only difference is you reference one cell where I have a range. I am focusing on column M.

2023.02.13 FAB Tracking & Reporting Sheet.xlsx
	I	J	K	L	M	N
23	44911	44931	44931	44931	44944	44931
24	44914	44931	44931	44931	44944	44931
25	44876	44880	44881	44882	44900	44900
26	44882	44882	44900	44907	44911	44907
27	44887	44900	44900	44907	X	44907
28	44900	44907	44931	44931	44944	44931
29	44901	44907	44911	44911	44911	44911
30	44901	44907	44911	44911	44911	44911
31	44908	44931	44931	44931	44944	44931
32	44907	44907	44931	44931	44944	44931
33	44924	44931	44944	44944	44956	44944
34	44924	44931	44944	44944	44956	44944
35	44930	44944	44944	44944	44950	44945
36	44909	44931	44931	44931	44945	44931
37	44915	44931	44944	44944	44944	44944
38	44915	44931	44944	44944	44945	44944
39	44915	44931	44944	44944	44945	44944
40	44959	44931	44945	44945	WIP	44945
41	44929	44944	44945	44945	WIP	44945
42	44900	44907	44931	44931	44944	44931
43	44915	44931	44944	44944	44944	44944
44	44916	44931	44944	44944	WIP	44944
45	44917	44931	44944	44944	WIP	44944
46	44922	44931	44944	44944	44951	44944
47	44922	44931	44944	44944	44951	44944
The Time Line CURRENT

Cell Formulas
Range	Formula
I23:I47	I23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),1)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),1))
J23:J47	J23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),2)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),2))
K23:K47	K23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),3)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),3))
L23:L47	L23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),4)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),4))
M23:M47	M23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5))
N23:N47	N23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),6)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),6))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M23	Expression	=ISTEXT($M$23:$M$47)	text	NO
M23	Expression	=AND(ISNUMBER($M$23:$M$47),$M$23:$M$47<>"")	text	NO

 

[etaf]

you are referencing a range not a single cell ???
so select M23:M47
and not just M23

then use
=ISTEXT($M23)
NOTE the $ is only on the column NOT the row
and then
=AND(ISNUMBER($M23),$M23<>"")

thats the difference

look at my conditional formatting box compared to you on the XL2BB

Mine

Cell	Condition	Cell Format	Stop If True
Cells with Conditional Formatting
A2:A16	Expression	=ISTEXT(A2)	text	NO
A2:A16	Expression	=AND(ISNUMBER(A2),A2<>"")	text	NO

yours

Cell	Condition	Cell Format	Stop If True
Cells with Conditional Formatting
M23	Expression	=ISTEXT($M$23:$M$47)	text	NO
M23	Expression	=AND(ISNUMBER($M$23:$M$47),$M$23:$M$47<>"")	text	NO

 

[itsrich]

I realized we did not need as much of the spreadsheet to have this discussion.

My Excel Just shut down and went to the recovery of all my spreadsheets. I am going to have to put this down until tomorrow or the next day. I have to figure out what was lost. I have lost everything we have talked about here.

This is where I left off. Not sure why the conditional formatting is formatting numbers as text.

2023.02.13 FAB Tracking & Reporting Sheet.xlsx
	M
23	44944
24	44944
25	44900
26	44911
27	X
28	44944
29	44911
30	44911
31	44944
32	44944
33	44956
34	44956
35	44950
36	44945
37	44944
38	44945
39	44945
40	WIP
41	WIP
42	44944
43	44944
44	WIP
45	WIP
46	44951
47	44951
The Time Line CURRENT

Cell Formulas
Range	Formula
M23:M47	M23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M:M	Expression	=ISTEXT($M23:$M47)	text	NO
M:M	Expression	=AND(ISNUMBER($M23:$M47),$M23:$M47<>"")	text	NO

 

[etaf]

now your selection is out of sync with the formula
you are selecring all of the rows in column M
so it starts at row 1 and then colours
But the test is M23 - and you have selected an array

so still not the correct entry for conditional formatting - the formula does not have an array

so when you select say M1:M20 - and have a formula like =M1="fred"
conditional formatting will start looking at M1, as thats whats selected and test to see if that sell M1 as thats in the formula contains fred
it then moves on to the next cell
M2
and the moves to the next cell
M3
and stops at M20 - as thats what you have selected

so now the formula should be

=ISTEXT($M1)
=AND(ISNUMBER($M1),$M1<>"")

as i have done in my example
i selected A2 to A16
and then the formula refers to A2


for example

Book2
	D
1
2
3
4
5	fred
6
7
8
9
10
11
12
13
14
15
16
17
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D15	Expression	=D1="fred"	text	NO

I selected
$D$4:$D$15
and then the formula

is D1=Fred

Now thats out by 3 rows
the selection starts at row4 - but the formula test to see if fred is in D1
then moves to D2 - selection moves to D5
then moves to D3 - selection moves to D6
then moves to D4 - selection moves to D7
then moves to D5 - selection moves to D8
so D5 does have fred - BUT the selection is now at D8 and so highlights the wrong cell
because the rows selected are out of sync with the formula

as we selected D4;D15
the formula starts at D4
=D4 = fred

hope that makes sense