Nov 01 2021 01:25 PM
Hello
I'm trying to put together some custom formulas to filter and put labels for some values I could do with some help for:
1. Trying to combine the two IF formulas below so that one cell shows a warning / text
Cells involved:
G2 - current mileage value
K2 - mileage value that service is due
Formula so far for just mileage:
=IF(G2<K2, "SERVICE OVERDUE", "SERVICE IN DATE")
Need to also add that IF the service date is overdue that this formula
H2 - service due date
Formula for service date:
=IF(H2-TODAY()<1,"VM OVERDUE","VM IN DATE")
How do I combine these? So that if either condition is not met then it says OVERDUE
2. I'm trying to get a formula to work that will note when vehicle maintenance is outside of its allowed time period for the following conditions:
If VM is completed within 10 days of todays date reads "VM IN DATE"
From within 10 - 20 days from todays date reads "VM DUE SOON"
From over 20 days from todays date "VM OVERDUE"
L2 - VM last completed date
Thanks
Nov 01 2021 03:05 PM
For the second it could be
=LOOKUP( L2, {0,10,20}, {"VM IN DATE", "VM DUE SOON", "VM OVERDUE"} )
First one I didn't catch. They have no common cells, if to take one condition another one will be fully ignored, even if to combine.
Nov 02 2021 09:04 AM
Thanks for this - it doesn't seem to work for the dates - do I need to format the cells or to make it for a date?