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.
