IF Excel forumula help

Copper Contributor

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

 

 

 

2 Replies

@DCexcelhelp 

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.

@Sergei Baklan 

 

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?