Conditional formatting formula character limits

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
250 characters plus sounds a lot of referencing, maybe it can be made smaller, if you post it
 

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
Sure, thanks. As background it's a tool that's being used for setting up scheduling on a 24 hour unit. The formula accounts for shift times (for example "730-1600" and length of shift, in this case, 8 hours). Here's what I'm trying to enter. It IS a cumbersome formula, I agree, but in shorter form it has worked. Open to any suggestions ...

=IF(OR(D4="730-1600",D4="1530-2400",D4=800,D4="0800 2330",D4="730-1600 r",D4="1530-2400 r",D4="0800 D"),8,(IF(OR(D4="730-2000",D4="730-2000 r",D4="1130-2400",D4="1130-2400 r",D4="0800 1930"),12,(IF(OR(D4=1930),4,(IF(OR(D4=2330,D4="D 2330",D4="d",D4="dr"),0,"?")))))))

There are spaces (for example in the first occurrence of "0800 2330" there are 10 spaces to account for appearance on the printed document) which, I think, count as characters(?).

Thanks, again for suggestions.

John
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
i guess this is a typo

="1530-2400",D4=800,D4="0800 2330",

numbers or text in the same cell
 

johnboston

New Member
Joined
Mar 11, 2011
Messages
23

ADVERTISEMENT

Yes, the 800 is supposed to be in quotes(?). I started this project months ago and it was recently revisited. It seems though it didn't matter whether the 800 was in quotes or not. I could certainly be mis-remembering on that.

I just checked it again with or without quotes before sending this and it didn't seem to make a difference in the result. It ought to be in quotes, though, is that one suggestion?

I guess, as additional background, what the spreadsheet is being used for is ... people request their shifts from a dropdown menu. For the working model I started out using, for example, the 5 most frequently requested shifts. When the sheet went active and began being used by more individuals the request was made, for example, for the next 5 most frequently requested shifts, as well. Meaning now, for example, I had to include 10 choices in the dropdown and update formulas.

Hope all this makes sense.

Appreciate your thoughts on this.

Thanks.

John
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
i'm not good at those style formulas, some very tuned in people here, know these
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

John,

There are ways we can possibly reduce that formula, or at least find a workaround. Can you please post some of your sample data, as well as describe exactly what your criteria is for the conditional format?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
I'm not sure this is actually CF, just shift patterns to leave a marker, 4, 8, 12 and 0
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John
Those little boxes are called refedits and yes, they are limited to 255 characters.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
P.S.

It looks like you want to use the "Formula Is" option but you're still limited to 255 characters in the formula.

If you could provide a more detailed explanation of what you want to format we can probalby come up with something.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top