Excel help - IF and DATE formulas

[excelhelpmeplease]

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

 

[etaf]

you can use an OR() for either cndition
BUT

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"

IF G2 is 1000 miles and K2 is 5000 then G2 is less than K2 and so Service is in date - or am i missreading?

=IF(H2-TODAY()<1,"VM OVERDUE","VM IN DATE")

=IF ( OR ( G2 < K2 , H2-TODAY()<1 ) , "OverDue" , "IN Date"

But you then have 3 conditions for date
So probably a Nested IF

=IF ( G2 < K2 , "OverDue", IF (

The Date if H2 says 1 nov
what date is the Overdue 20th Nov , overdue
Then on 10th Nov to 20th - VM Due soon

can you give examples of the dates, i'm a little confused

=IF ( OR ( G2 < K2 , (H2-TODAY()-20)<1 )

 

[excelhelpmeplease]

Hello

Thanks for your help - that first formula is great and works well.

If I wanted to add a warning to the first formula that 'SERVICE DUE SOON' if the date gets within a month or if the mileage gets within 1,000 miles off the service mileage would this be possible?


With regards to the second formula, an example taking TODAYS date 03/11/2021

If VM is completed within 10 days of todays date reads "VM IN DATE" - EG completed and dated within the last 10 days from 24/10/2021

From within 10 - 20 days from todays date reads "VM DUE SOON" - EG completed within 14/10/2021 to 24/10/2021

From over 20 days from todays date "VM OVERDUE" - EG completed before 14/10/2021


Thanks for the help

 

[etaf]

i'm still not following your example, BUT it is very possible to do with a nested IF , just I need to understand the logic , not following yours

Regardless at the moment of todays date
you have a cell with the mileage required for next service and this is in K2 , Miles Service is Due
then you have a cell of the date the service is due - H2
Current Mileage in G2

Not sure what cell the formula is going in
Lets say - M2 - STATUS

for mileage - we want the TEXT
SERVICE DUE SOON
When within 1000 miles
AND
SERVICE OVER DUE
When over mileage

=IF( K2-G2 < 0, "service overdue", IF( K2-G2 <1000 , "Service Due Soon", "Service in date"))
now we need to add the criteria based on todays date
This i dont understand
We could add the date part to the above
=IF( OR( K2-G2 < 0, "service overdue"

But I would expect over due to be if todays date is greater than the service date H2

IF( OR( K2-G2 < 0, H2<today() ) "service overdue"
Then we could say 20 days due soon

IF( OR( K2-G2 < 0, H2<today() ) "service overdue" , IF( OR ( K2-G2 <1000 , H2-20< today() ) , "Service Due Soon",
here i have combined the mileage and the dates

I'm just not following the dates
really sorry , seems the earlier the date is the status seems wrong
But i may reread tomorrow and see what you mean

 

[etaf]

If VM is completed within 10 days of todays date reads "VM IN DATE" - EG completed and dated within the last 10 days from 24/10/2021
From within 10 - 20 days from todays date reads "VM DUE SOON" - EG completed within 14/10/2021 to 24/10/2021
From over 20 days from todays date "VM OVERDUE" - EG completed before 14/10/2021

So this is what I think the above means

Is this how you expect the results to be ?
I'm sure i misunderstanding the dates

Also which of the 3 conditions would you want the mileage to be added

Book7
	E	F	G	H
1	before 20	Oct-20	10days	Date Service Due
2			VM in Date	11/14/21
3			VM in Date	11/13/21
4			VM in Date	11/12/21
5			VM in Date	11/11/21
6			VM in Date	11/10/21
7			VM in Date	11/9/21
8			VM in Date	11/8/21
9			VM in Date	11/7/21
10			VM in Date	11/6/21
11			VM in Date	11/5/21
12			VM in Date	11/4/21
13			VM in Date	11/3/21
14			VM in Date	11/2/21
15			VM in Date	11/1/21
16			VM in Date	10/31/21
17			VM in Date	10/30/21
18			VM in Date	10/29/21
19			VM in Date	10/28/21
20			VM in Date	10/27/21
21			VM in Date	10/26/21
22			VM in Date	10/25/21
23		VM Due Soon		10/24/21
24		VM Due Soon		10/23/21
25		VM Due Soon		10/22/21
26		VM Due Soon		10/21/21
27		VM Due Soon		10/20/21
28		VM Due Soon		10/19/21
29		VM Due Soon		10/18/21
30		VM Due Soon		10/17/21
31		VM Due Soon		10/16/21
32		VM Due Soon		10/15/21
33	VM Overdue			10/14/21
34	VM Overdue			10/13/21
35	VM Overdue			10/12/21
36	VM Overdue			10/11/21
37	VM Overdue			10/10/21
38	VM Overdue			10/9/21
39	VM Overdue			10/8/21
40	VM Overdue			10/7/21
41	VM Overdue			10/6/21
42	VM Overdue			10/5/21
43	VM Overdue			10/4/21
44	VM Overdue			10/3/21
45	VM Overdue			10/2/21
46	VM Overdue			10/1/21
47	VM Overdue			9/30/21
Sheet4

Cell Formulas
Range	Formula
E2:E47	E2	=IF(H2<TODAY()-20,"VM Overdue","")
F2:F47	F2	=IF(AND(H2>=TODAY()-20,H2<TODAY()-10),"VM Due Soon","")
G2:G47	G2	=IF(H2>=TODAY()-10,"VM in Date","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:H47	Expression	=$H2=TODAY()	text	NO

 

[excelhelpmeplease]

So this is what I think the above means

Is this how you expect the results to be ?
I'm sure i misunderstanding the dates

Also which of the 3 conditions would you want the mileage to be added

Book7
	E	F	G	H
1	before 20	Oct-20	10days	Date Service Due
2			VM in Date	11/14/21
3			VM in Date	11/13/21
4			VM in Date	11/12/21
5			VM in Date	11/11/21
6			VM in Date	11/10/21
7			VM in Date	11/9/21
8			VM in Date	11/8/21
9			VM in Date	11/7/21
10			VM in Date	11/6/21
11			VM in Date	11/5/21
12			VM in Date	11/4/21
13			VM in Date	11/3/21
14			VM in Date	11/2/21
15			VM in Date	11/1/21
16			VM in Date	10/31/21
17			VM in Date	10/30/21
18			VM in Date	10/29/21
19			VM in Date	10/28/21
20			VM in Date	10/27/21
21			VM in Date	10/26/21
22			VM in Date	10/25/21
23		VM Due Soon		10/24/21
24		VM Due Soon		10/23/21
25		VM Due Soon		10/22/21
26		VM Due Soon		10/21/21
27		VM Due Soon		10/20/21
28		VM Due Soon		10/19/21
29		VM Due Soon		10/18/21
30		VM Due Soon		10/17/21
31		VM Due Soon		10/16/21
32		VM Due Soon		10/15/21
33	VM Overdue			10/14/21
34	VM Overdue			10/13/21
35	VM Overdue			10/12/21
36	VM Overdue			10/11/21
37	VM Overdue			10/10/21
38	VM Overdue			10/9/21
39	VM Overdue			10/8/21
40	VM Overdue			10/7/21
41	VM Overdue			10/6/21
42	VM Overdue			10/5/21
43	VM Overdue			10/4/21
44	VM Overdue			10/3/21
45	VM Overdue			10/2/21
46	VM Overdue			10/1/21
47	VM Overdue			9/30/21
Sheet4

Cell Formulas
Range	Formula
E2:E47	E2	=IF(H2<TODAY()-20,"VM Overdue","")
F2:F47	F2	=IF(AND(H2>=TODAY()-20,H2<TODAY()-10),"VM Due Soon","")
G2:G47	G2	=IF(H2>=TODAY()-10,"VM in Date","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:H47	Expression	=$H2=TODAY()	text	NO

Hello

Thanks so much for your help - I'll try and explain a bit further below...

I've managed to get both formulas working to show either of two options

In cell J2:

=IF(OR(G2<K2,H2-TODAY()<1),"SERVICE OVERDUE","SERVICE IN DATE")

How can I add on:
1. if the service is within 1000 miles of the service due mileage (G2)
OR
2. if the service is within 20 days of the due date (H2)
To display 'SERVICE DUE SOON'

In cell M2:

=IF(L2>=TODAY()-15,"VM in Date","VM OVERDUE")

How can I add on:
1. If VM is due soon - 10 days have elapsed since it was done - between days 10-15 (15 is shows OVERDUE)
To display 'VM DUE SOON'

Thanks again

your help.




I've managed to get the formulas working

 

[etaf]

I've managed to get the formulas working

so does that mean its all sorted now ?

If NOT
I still dont follow the terms
IN J2 you have an IF statement
=IF(OR(G2<K2,H2-TODAY()<1),"SERVICE OVERDUE","SERVICE IN DATE")
Then you want to add in
'SERVICE DUE SOON'
So that nested IF would be in place of Service in Date
1. if the service is within 1000 miles of the service due mileage (G2)
OR
2. if the service is within 20 days of the due date (H2)
To display 'SERVICE DUE SOON'

=IF(OR(G2<K2,H2-TODAY()<1),"SERVICE OVERDUE",IF ( OR( G2 - K2 <= 1000, H2 > today() - 20 ) , "SERVICE DUE SOON", "SERVICE IN DATE")

 

[etaf]

these are completely different formulas and results in different cells
if that is the case

In cell M2:

=IF(L2>=TODAY()-15,"VM in Date","VM OVERDUE")

How can I add on:
1. If VM is due soon - 10 days have elapsed since it was done - between days 10-15 (15 is shows OVERDUE)
To display 'VM DUE SOON'

Again a Nested IF, to add VM DUE SOON
=IF(L2>=TODAY()-15,"VM in Date","VM OVERDUE")

=IF(L2>=TODAY()-15,"VM in Date", IF ( AND ( L2 > = today() -15, L2 < = today() -10 ) , "VM DUE SOON","VM OVERDUE")


Can you use XL2BB add-in , see signature or menu on how to do this
OR put the sample spreadsheet on to a share like dropbox/onedrive etc

And put some manual entered examples of dates and mileage with the results needed for all the possible results
would help a lot here