2

I am creating a spreadsheet for my local parish church to keep track of borrowed DVDs:

The DVDs have to be returned 14 days after they are borrowed. If the current date is over 14 days after the date borrowed then I would like the respective cell in the overdue column to show Yes; however if the DVD is returned, then the respective cell (in overdue column) should be blank. The formula I currently use is: =IF(D10<(TODAY()-14), "Yes","") where D10 is the Date borrowed.

The problem with this formula is that "Yes" appears if the Date borrowed cell is blank and It continues to indicate "Yes" (in over due column) even if the DVD is returned.

Any feedback would be much appreciated.

2 Answers 2

1

You can check for multiple conditions using the AND function. Add 2 additional conditions;

  1. Cell D10 contains a date, (greater than your starting date).
  2. The DVD was not returned.

Suppose you enter "Returned" in column F when a DVD is returned, you can use the following:

=IF(AND(D10>1/1/2000,D10<(TODAY()-14),F10<>"Returned"),"Yes","")
0

As your problem arises from having the cell D10 blank when the DVD is returned you have to make two verifications:

  • Check if the cell is blank, otherwise
  • Check if the date is over 14 days.

So you have to use nested IFs, just like this:

=IF(D10="","",IF(D10<(TODAY()-14), "Yes",""))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .