Help with Formula
So here is my issue. I am Military and have to present a spreadsheet tomorrow but I want to add a formula that makes it easier for me and my superiors to use. It is about Military Vehicle Scheduled Maintenance (Service). There is a column labeled "Due Date", this is the actual day that the vehicle needs to come in and the mechanics do a service on it. Depending on the type of service it is, it will have a 10% variance allowed so in other words if it is an annual service (365 Days) we are allowed to service it 36 days before the due date or 36 days after the due date. Therer is also a column for the status of the vehicle that shows whether or not the vehicle is overdue, Awaiting Shop, In Shop, and Complete. I already have conditional formatting on the rows to change to a specific color which is determined by the status. What I want to do is have a formula added that will automatically change the status as well as the color of the row.
| Attachment | Size |
|---|---|
| Service Tracker.xlsx | 19.48 KB |
Do you still require an answer?
Hi Soldier1979,
Am I correct in thinking that you are using the (+)Variance date to dictate if the service is overdue? if so use the following:
=If(today()>F3,"Overdue","Awaiting Shop")
Unfortunately there is nothing on the spreadsheet to drive "In Shop" or "Complete". However you can use data validation to create a list for all 4 options in this column, remove the error alert then paste the above formula into this column. This will stop anyone from mistyping any of the options so your conditional formating will always work for colour coding
Regards
Mark
Do you still require an answer?
Hi Soldier1979,
Am I correct in thinking that you are using the (+)Variance date to dictate if the service is overdue? if so use the following:
=If(today()>F3,"Overdue","Awaiting Shop")
Unfortunately there is nothing on the spreadsheet to drive "In Shop" or "Complete". However you can use data validation to create a list for all 4 options in this column, remove the error alert then paste the above formula into this column. This will stop anyone from mistyping any of the options so your conditional formating will always work for colour coding
Regards
Mark