Google Sheets – How to Add Condition to Formula to Not Trigger if Another Cell is Blank

conditional formattingformulasgoogle sheets

I'm new to adding formulas etc. in Google Sheets, and I'm currently trying to set up a library borrowing/returns spreadsheet.

I have three columns formatted to dates: 'G' (date borrowed), 'H' (date due) and 'I' (date returned).

In 'H' I have the following formula to count 20 days from the date in column 'G', whilst also not firing if 'G' is blank:

=ArrayFormula(if(ISBLANK(G6),"",DATEVALUE(G6)+20))

This works great.

I also have conditional format to highlight 'H' in red if the date becomes overdue, which is:

Value is less than
=TODAY()

This does the job initially, except that I need the format which makes 'H' highlight red NOT to fire if there is a return date entered in column 'I' (otherwise every entry will eventually turn red, and that's not really helpful for what I'm doing!).

I'm having a hard time figuring it out.

I feel it should be simple to add an ISBLANK type command into the =TODAY() format, but I'm clearly missing something.

Any help would be greatly appreciated.

Best Answer

a custom formula you are after:

=IF(I5="",COUNTIF(H5,"<"&TODAY()),)

enter image description here