using conditional format and seeing the wrong text in the formula bar

[still learning]

Hi
I use conditional format for the following:
I put “Pending” in a cell (F) and when the date changes to today or after, it changes to “payment”

It worked perfectly for at least a year until Jan 1, 2021

Now conditional format changes the text BUT any cell that references it …doesn’t............including the formula bar...and If statfements
I shortened the spreedsheet to make it easier to understand

mike
sorry, I don't know how th make the row width's less after I used the L2BB

sample problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		this is a sample of my problem
2		It's supposed to >>>		I put in "pending" and when the date is today and after, it changes to "payment" >>> Row N keeps a runninh total
3			it has worked trouble free all last year and started not working on Jan 1,2010
4
5	dates formatted as mm/dd/yy				this row has conditional format				=IF(AND(B7="abc",F7="charge"),+C7,IF(AND(B7="abc",F7="payment"),-C7,IF(AND(B7="abc",F7="credit"),-C7,"")))
6
7	12/12/20	abc	100.00	statement in full	payment	=AND(a7<=today(),F7="pending")….format…custom.. ;;;"payment"						-100
8	12/13/20	abc	7.99	book	charge							7.99
9	12/21/20	abc	100.00		charge							100
10	12/24/20	abc	3.99	book	charge	put in "pending" and the format changes it to "payment"						3.99
11	12/28/21	abc	19.03		charge							19.03
12	12/30/20	abc	2.99	book	charge							2.99
13	01/01/21	abc	156.12	in full	payment		formula bar shows pending						If statement returns nothing
14	01/03/21	abc	47.44		charge							47.44
15	01/04/21	abc	100.00	in full	payment		formula bar shows pending						If statement returns nothing
16	01/21/21	abc	100.00	in full	payment		formula bar shows pending						If statement returns nothing
17										after I formatted F7, I used the "format painter" and dragged it down to F16
18						format changes this to "payment" even though the date is in the future
19
20						It worked good from 12/20/20 and before
21						I tried changing the date format which didn't helf
22						computer date and time are correct
23
24				I shortened the spreesheet to show that A7:N7 works fine
25
26				the rest does not even with the same formaula
27
Sheet1

Cell Formulas
Range	Formula
L7:L16	L7	=IF(AND(B7="abc",E7="charge"),+C7,IF(AND(B7="abc",E7="payment"),-C7,IF(AND(B7="abc",E7="credit"),-C7,"")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:E16	Expression	=AND(XFB8<=TODAY(),E8="pending")	text	NO
E7	Expression	=AND(XFB7<=TODAY(),E7="pending")	text	NO

 

[Peter_SSs]

Now conditional format changes the text BUT any cell that references it …doesn’t............including the formula bar...and If statfements

That is because formatting (condition or otherwise) does not change the underlying value in the cell, it only changes the appearance of the cell. It has always worked like that.

It is just like cell A1 below. I have formatted as currency with 2 decimal places so in that cell you see the appearance of a "$" sign and a decimal point followed by two zeros, but actually in the cell is still just 100 as you see in the formula bar.

 

[still learning]

Hi Peter_SSs
Happy New Year
That doesn't explain why E7,which is formatted the same shows "payment" in the formula bar and the formula in L7 works.
It also doesn't explain why the format worked before.


mike

 

[Peter_SSs]

That doesn't explain why E7,which is formatted the same shows "payment" in the formula bar ...

Perhaps you haven't uploaded the actual information in your worksheet then?
I copied your XL2BB sheet to mine and E7 does contain "payment" so no surprise to me that "payment" shows in the formula bar.
If E7 actually contains something else, then perhaps you could send us that XL2BB sheet so that we can try to understand what you are saying.

 

[still learning]

...E7 does contain "payment"...
BUT E7 originally had "pending" till conditional format changed it to "payment". (that's the point I was trying to make)
For at least a year, I had no problem, them at the beginning of this year it started.
L7 has an if statement that looks for payment and pending
It recognizes it in E7 but not the others.
Every thing in E:E16 is conditionally formatted to change pending to payment if the date is today or previous
It worked fine till January
I noticed it because the running total (E) was wrong
If I clear the format and type in payment, the if statement works
It seems that somehow the spread sheet stopped recognized the format change when before it did.
I changed the format of the date which didn't help
I put an if statement in a blank cell that refereed to E7. (.which was changed using conditional format) .
=if(E7="payment",1,2)
it returned 1
I did it for E13
it returned 2
I typed in "payment in E13 and it returned 1
I cleared conditional format for E13 and retyped it. (originall, I use the paint bruch and copied the format from E7),
didn't work

sorry to give you a long and boring review of what i did !!!!!!!!!!!!!!!!
Till i fix it, i will just change pending to payment myself
If I get a chance this week end, I'll start a new workbook and type in the info instead of copy and paste and see what happens. It'll be Monday before i get back here

I DO appreciate your help. you've been a great help it the paste, too


mike

 

[Peter_SSs]

Sorry, but Conditional Format has never actually changed the value in a cell. That is why it is called Conditional Format, it just changes the appearance, the underlying value in the cell remains the same.
I don't know what you had before, but I can assure you that you did not have Conditional Formatting changing the actual value in a cell.

BTW, what are the references to column XFB in the CF formulas in post 1?

 

[still learning]

Hi Pete
That reference was an OPPS. it was supposed to refer to B
I changed it in the sample and it didn't help
I opened a new workbook and type in the info and as you said. it only changed the appearance of the cell.
It is weird because it seem to work before this year
What I going to do is rerwrite the sheet. glad its the beginning of the year. anything before is for reference only
What I do is enter charges and credits for my credit cards on sheet1, then on sheet2,sheet3..etc. I use Advance filter to keep each card separate
the formulas and if statements refer to each card.
the running totals are on sheet1
maybe i'll have the totals on each sheet and just have the current ones on sheet1

Thanks for your time and input

mike

 

[Peter_SSs]