2

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.

1

a custom formula you are after:

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

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.