Reputation: 25
I have created a simple Google Form for end-users to enter their email, check-out date and return date for a loaner computer. I was able to get the formula in Column H =IF(E2=E2,MAX(TODAY()-E2,0),0)
to display the Days Overdue which checks against column E (Return Date). An apps script then runs every day and sees if the Days Overdue is greater than 0, if so, it sends a reminder to turn in email.
My issue is Column H has static cells assigned, I don't want to have to go in and copy the formula to the next cell in a newly submitted row. I have added the following to the Column H header trying to auto-populate the formula to no avail.. I've been working with a few examples but can't seem to get this to work.
={"Days Overdue";ARRAYFORMULA(IF(H2:H="",,IF(E2=E2,MAX(TODAY()-E2,0),0)))}
Where Column H is the "Days Overdue" row and Column E is the Return Date row. How do I dynamically change E2, to E3, E4 etc depending on the row?
Upvotes: 1
Views: 2476
Reputation: 10269
Try this in H2:
=arrayformula(if($E2:$E<>"",IF($E2:$E=$E2:$E,TODAY()-$E2:$E,0),""))
Upvotes: 1