using IF statements to modify dates when referencing text

[Enzo_Matrix]

I am creating a new log book for our company and I want to have the 'calibration due date' modify automatically depending on two indicators (can't think of a better term).

So I want A3 to change it's date depending on:
A1's text (Gauge,Fork lift....)
A2's date (calibration date)

My hope is that it will work something along the lines of:

A1 = gauge
A2 = February 1, 2018
A3 = 12 months after A2

OR

A1 = Compressor
A2 = February 1, 2018
A3 = 1 month after A2

so IF A1='text' then A3=12(A2) or something to that effect.

 

[etaf]

What are the different Texts are there in A1 and the corresponding A3 date
ie

Gauge = A2+12months
Compressor = A2 + 1 month
Fork = ?
Lift =?
???

You maybe a able to use a nested IF
OR
a lookup value
and then use Date()
if you provide the complete list - we can see whats the best solution

 

[Enzo_Matrix]

I am also using Excel 2013

Gauge = A2 + 12 months
Compressor = A2 + 1 month
Forklift = A2 + 1 month
Crimper = A2 + 1 Month
Welding Machines = A2 + 1 Month

 

[etaf]

so the only one that is 12 months is gauge and everything else is a month

can the cell be blank

=IF( A1="guage", DATE(YEAR(A2),MONTH(A2)+12,DAY(A2)), DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))

BUT
a anything else in the cell will be added a month

otherwise, we would need to use some tests to see what was in cell a1 and possibly A2 if blank

 

[Joe4]

Note that using the EDATE function can simplify this:

[COLOR=#333333]=IF( A1="guage", DATE(YEAR(A2),MONTH(A2)+12,DAY(A2)), DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))[/COLOR]

to this:

[COLOR=#333333]=IF(A1="guage",EDATE(A2,12),EDATE(A2,1))[/COLOR]

See: https://www.techonthenet.com/excel/formulas/edate.php

 

[Enzo_Matrix]

Thanks!

That formula does work extremely well, but the cells can be blank as the log sheet i'm creating does not have any data in it as of yet.

With no data in A2, A3 = January 31, 1900.

Is there a way to make this blank until data is added to A2?

 

[Joe4]

Sure, just nest it in another IF statement, i.e.

[COLOR=#333333]=[/COLOR][COLOR=#ff0000]IF(A2<>""[/COLOR][COLOR=#333333],IF(A1="guage",EDATE(A2,12),EDATE(A2,1))[/COLOR][COLOR=#ff0000],""[/COLOR][COLOR=#333333])[/COLOR]

 

[Enzo_Matrix]

Thank you, works perfectly!

 

[Enzo_Matrix]

I tried to find a way to use conditional formatting on this code so that I can create reminders of due dates by flagging cells.

=IF($A1<>"", IF($A1="Gauge",EDATE($A2,12),EDATE($A2,1)),"")

Is there a way to modify the above code to include a conditional formatting (lack of better terminology) to trigger reminders 1-2 weeks before the date in A3?

 

[Joe4]

Conditional Formatting you be separate of the formula.

If you wanted the date being returned that formula to be Conditional Formatting if the date is less than two weeks away, then apply this Conditional Formatting formula to the cell:

=AND(A3<>"",(A3-TODAY())<14)

and choose your formatting option.