still learning
Well-known Member
- Joined
- Jan 15, 2010
- Messages
- 784
- Office Version
- 365
- Platform
- Windows
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
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 |