Can you run a formula if and only if the number is negative?
Here is what I am trying to do:
Each month we want to assign a score to the person doing the tasks based on meeting due dates.
Each month they start with 20 points. (Maximum of 20 points)
For every day past the due date, they lose 2 points.
I want a summary field where I can subtract the lost points from 20, but not add points if they complete ahead of schedule.
Here is what I tried:
The Days to Complete column has a simple formula: Date Due - Date Complete
The red circle shows the number for a task 1 day overdue. That works.
The Score Column has a simple formula that is =Days to Complete * -2 - The negative 2 produces a positive number to subtract the loss of 2 points.
Then my summary field, "Credit Card receipts" shows my formula 20 - Score.
The problem is if the person completes the task ahead of schedule they will get a positive number in the Days to Complete column and then the formula would produce a score of 22 in the summary field.
Is there a better way to do this? Or are there conditional formulas that will allow me to do this so that I don't have to manually complete this each month?
Help Article Resources
Check out the Formula Handbook template!