IF Excel forumula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2910571%22%20slang%3D%22en-US%22%3EIF%20Excel%20forumula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2910571%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20put%20together%20some%20custom%20formulas%20to%20filter%20and%20put%20labels%20for%20some%20values%20I%20could%20do%20with%20some%20help%20for%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Trying%20to%20combine%20the%20two%20IF%20formulas%20below%20so%20that%20one%20cell%20shows%20a%20warning%20%2F%20text%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20involved%3A%3C%2FP%3E%3CP%3EG2%20-%20current%20mileage%20value%3C%2FP%3E%3CP%3EK2%20-%20mileage%20value%20that%20service%20is%20due%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20so%20far%20for%20just%20mileage%3A%3C%2FP%3E%3CP%3E%3DIF(G2%3CK2%3E%3C%2FK2%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20to%20also%20add%20that%20IF%20the%20service%20date%20is%20overdue%20that%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EH2%20-%20service%20due%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20for%20service%20date%3A%3C%2FP%3E%3CP%3E%3DIF(H2-TODAY()%26lt%3B1%2C%22VM%20OVERDUE%22%2C%22VM%20IN%20DATE%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20combine%20these%3F%20So%20that%20if%20either%20condition%20is%20not%20met%20then%20it%20says%20OVERDUE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20I'm%20trying%20to%20get%20a%20formula%20to%20work%20that%20will%20note%20when%20vehicle%20maintenance%20is%20outside%20of%20its%20allowed%20time%20period%20for%20the%20following%20conditions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20VM%20is%20completed%20within%2010%20days%20of%20todays%20date%20reads%20%22VM%20IN%20DATE%22%3C%2FP%3E%3CP%3EFrom%20within%2010%20-%2020%20days%20from%20todays%20date%20reads%20%22VM%20DUE%20SOON%22%3C%2FP%3E%3CP%3EFrom%20over%2020%20days%20from%20todays%20date%20%22VM%20OVERDUE%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EL2%20-%20VM%20last%20completed%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2910571%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2911010%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Excel%20forumula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2911010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202719%22%20target%3D%22_blank%22%3E%40DCexcelhelp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20second%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLOOKUP(%20L2%2C%20%7B0%2C10%2C20%7D%2C%20%7B%22VM%20IN%20DATE%22%2C%20%22VM%20DUE%20SOON%22%2C%20%22VM%20OVERDUE%22%7D%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFirst%20one%20I%20didn't%20catch.%20They%20have%20no%20common%20cells%2C%20if%20to%20take%20one%20condition%20another%20one%20will%20be%20fully%20ignored%2C%20even%20if%20to%20combine.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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?