Conditional Formatting in Google Sheets

Question

The shorter question to spare you from reading more than you need to

I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 characters (yes I know it's unmaintainably long - I don't really have a choice because {gestures vaguely towards management group} circumstances). Is this violating a hard character limit for conditional formatting's custom formula field? I can't find a number anywhere in the documentation or in other questions on here.

If the answer to that is "Yes, this is too long!" Then just say that and all will be solved. If the answer to that is "No, you should be fine" then keep reading.

The long explanation for if the short question's answer is "No you should be fine"

I'm using a google sheet (google spreadsheet? docs spreadsheet? what's the proper vocab here?) to manage a job schedule (yes I know there's more appropriate software to do this but guess whose management team doesn't care!). The sheet lists the status of various print jobs and the day they need to print. I currently have conditional formatting with a custom formula applied to make the text in the row bold if the job is supposed to print tomorrow, today, or was supposed to have printed in the past but hasn't yet. This is to provide a quick visual indication that my department need to start pressing the account managers for more information about any specific job because it's scheduled to print soon. It looks something like

=and({status cell} = {relevant statuses}, today() >= {printdate} - 1)

There are a few extra conditionals in there for if today() is Friday and the job prints on Saturday, Sunday, or Monday since my department does not work on the weekends. It works great. But now management wants me to extend this weekend functionality so that if every day between today() and {printdate} is a day we're closed, bold the text. To solve this, I wrote the following formula:

=and(
    not(isblank($H3)),
    or(
        $A3 = "Approved", 
        $A3 = "Approved - Template"
        ),
    or(
        today() >= $H3 - 1,  # Prints tomorrow, today, Or in the past 

        and(  # Prints after 1 day closed
            today() = $H3 - 2,
            or(
                weekday($H3 - 1) = 1, 
                weekday($H3 - 1) = 7, 
                countif(range_Holidays, $H3 - 1) = 1
                )
            ),

        and(  # Prints after 2 days closed
            today() = $H3 - 3,  # 2 days between now and print
            and(  # all days in gap are holidays or weekends
                or(
                    weekday($H3 - 2) = 1, 
                    weekday($H3 - 2) = 7, 
                    countif(range_Holidays, $H3 - 2) = 1
                    ),
                or(
                    weekday($H3 - 1) = 1, 
                    weekday($H3 - 1) = 7, 
                    countif(range_Holidays, $H3 - 1) = 1
                    )
                )
            ),

        and(  # Prints after 3 days closed
            today() = H3 - 4,  # 3 days between now and print
            and(  # all days in gap are holidays or weekends
                or(
                    weekday($H3 - 3) = 1, 
                    weekday($H3 - 3) = 7, 
                    countif(range_Holidays, $H3 - 3) = 1
                    ),
                or(
                    weekday($H3 - 2) = 1, 
                    weekday($H3 - 2) = 7, 
                    countif(range_Holidays, $H3 - 2) = 1
                    ),
                or(
                    weekday($H3 - 1) = 1, 
                    weekday($H3 - 1) = 7, 
                    countif(range_Holidays, $H3 - 1) = 1
                    )
                )
            ),

        and(  # Prints after 4 days closed
            today() = H3 - 5,  # 4 days between now and print
            and(  # all days in gap are holidays or weekends
                or(
                    weekday($H3 - 4) = 1, 
                    weekday($H3 - 4) = 7, 
                    countif(range_Holidays, $H3 - 4) = 1
                    ),
                or(
                    weekday($H3 - 3) = 1, 
                    weekday($H3 - 3) = 7, 
                    countif(range_Holidays, $H3 - 3) = 1
                    ),
                or(
                    weekday($H3 - 2) = 1, 
                    weekday($H3 - 2) = 7, 
                    countif(range_Holidays, $H3 - 2) = 1
                    ),
                or(
                    weekday($H3 - 1) = 1, 
                    weekday($H3 - 1) = 7, 
                    countif(range_Holidays, $H3 - 1) = 1
                    )
                )
            )
        )
    )

A few notes:

• 'range_Holidays' is a named range containing all non-weekend days that we are closed.
• Column H contains the job's print date
• column A is the job status.
• we are never closed longer than 4 consecutive days.

I can provide a more detailed summary of this formula if you need/want.

Anyway

When brought on to a single line and removing all comments and extraneous whitespace brings this specific formula down to a length of 1068 characters.

When entered into a cell, it returns TRUE and FALSE when I expect it to under all test cases I've thrown at it.

The actual problem

The problem is when I paste the formula into the conditional formatting's formula box - the box turns red and a tooltip saying "Invalid formula" appears. If I remove the leading = sign, the error goes away but the formula isn't applying the formatting where I would expect it to (rows where it returned TRUE when pasted in a normal cell).

If length isn't the issue (which it isn't if you've read this far), the only other thing I could think of is that there's too many nested conditionals - but I'd like to rule that out before I put in the effort for a workaround. Any ideas what might be preventing this from working?

Answer 1

Maybe not the perfect answer, but have you considered setting up multiple conditional formatting rules each that apply the same formatting, but for the more granular conditions. For example, a rule for ' # Prints after 1 day closed', etc.

Answer 2

Regarding the character limit for a custom formula, it might be the same as the cell content which is 50,000 (fifty thousand) characters. As the space to write/read a custom formula is very limited I suggest that instead of using a single rule with a long custom formula use multiple rules or use Google Apps Script to edit the single formula. Another advantage of using Google Apps Script is that you could keep the comments by following JavaScript syntax.

Regarding the error, custom formulas can't use references or named ranges that points to another sheet. The workaround is to use INDIRECT (like INDIRECT("Sheet 1!A1").

Related

• Conditional Formatting based on another sheet's content
• Is there a specific limit to how long a formula can be and what is considered to be the limit?