Min Values minus 18 months

[Elizabeth]

I want to do this formula only 18 months instead of 547.5 days so it gives me
exact dates instead of ballpark? Does anyone know how?

=MIN(G1-547.5,H1,I1-547.5)

Thank you. Elizabeth

 

[Mike H]

Try

=MIN(G1-DATE(0,18,0),H1,I1-DATE(0,18,0))

Mike

 

[John C]

Two ways. Assuming the base date is in cell D3

1: =DATE(YEAR(D3),MONTH(D3)-18,DAY(D3))

2: =EDATE(D3,-18)

Note: The EDATE function is available if you have the Analysis ToolPak
add-in installed.

 

[Elizabeth]

I tried this formula but it comes up with 04/02/07 but shouldn't it be
02/28/07?

G H I J
08/31/08 2/28/08 09/01/08 =MIN(G1-DATE(0,18,0),H1,I1-DATE(0,18,0))

In line two I get 07/16/07 but I think it should be 06/14/07?

12/14/08 12/14/08 12/15/08 =MIN(G2-DATE(0,18,0),H2,I2-DATE(0,18,0))

 

[Elizabeth]

The computers at work don't let us install anything so the add-in isn't
possible for me. I don't understand how to put what you've given me into the
MIN statement?
Is this right? It appears to be giving me the right dates but I'm unsure
about sending it to my supervisor until I'm sure it works with all the
senerios.

=MIN(DATE(YEAR(G1),MONTH(G1)-18,DAY(G1)),H1,DATE(YEAR(I1),MONTH(I1)-18,DAY(I10)))

 

[Elizabeth]

Actually it appears to be off by one day in some cases but correct in most of
them Is that because of leap year?

This gives me 3/1/07 instead of 2/28/07

G H I J
08/31/08 2/28/08 09/01/08
=MIN(DATE(YEAR(G1),MONTH(G1)-18,DAY(G1)),H1,DATE(YEAR(I1),MONTH(I1)-18,DAY(I10)))

 

[Mike H]

Maybe

=DATE(YEAR(MIN(G1:I1)),MONTH(MIN(G1:I1))-18,DAY(MIN(G1:I1)))

Mike

 

[Elizabeth]

Wouldn't that also subtract 18 months from H1?

We started from:
=MIN(G1-18mths,H1,I1-18mths)

 

[Mike H]

Hmmm

=MIN(DATE(YEAR(G1),MONTH(G1)-18,DAY(G1)),H1,DATE(YEAR(I1),MONTH(I1)-18,DAY(I1)))

Mike

 

[Elizabeth]

This is kinda funny because that is exactly the answer I'm at with the other
person "John" who is helping me but it seems to be off by one day sometimes.
I pasted below the last post by me under John's answer.
---
Actually it appears to be off by one day in some cases but correct in most of
them Is that because of leap year?

This gives me 3/1/07 instead of 2/28/07
G H I J
08/31/08 2/28/08 09/01/08
=MIN(DATE(YEAR(G1),MONTH(G1)-18,DAY(G1)),H1,DATE(YEAR(I1),MONTH(I1)-18,DAY(I1)))

 

[John C]

You are experiencing issues because the number of days in a month isn't the
same all the time. You could check your Tools|Add-Ins and ensure that you
don't have the Analysis ToolPak is not added in. Many times, it is added in
unknown to the user. If it is, the EDATE function I described earlier works
perfectly. If not, I have an ugly non-VBA method of ensuring that you aren't
off by a day or 2. In a way, you still will be, as obviously 18 months prior
to 8/31/2008, I am assuming you are wanting to show 2/28/2007.

First, I created 2 variables, GcolDate and IcolDate. Go to menu
Insert|Name|Define, and this is what you can copy and paste for the variables:
GcolDate
=DATE(YEAR($G2),MONTH($G2)-18,MIN(DAY($G2),CHOOSE(MONTH(DATE(YEAR($G2),MONTH($G2)-18,IF(DAY($G2)<=28,DAY($G2),1))),31,IF(YEAR(DATE(YEAR($G2),MONTH($G2)-18,DAY($G2)))=2000,29,28),31,30,31,30,31,31,30,31,30,31)))
IcolDate
=DATE(YEAR($I2),MONTH($I2)-18,MIN(DAY($I2),CHOOSE(MONTH(DATE(YEAR($I2),MONTH($I2)-18,IF(DAY($I2)<=28,DAY($I2),1))),31,IF(YEAR(DATE(YEAR($I2),MONTH($I2)-18,DAY($I2)))=2000,29,28),31,30,31,30,31,31,30,31,30,31)))

It will not work for leap years 2400, 2800, 3200, etc, but I think it'll be
alright for your purposes.
After creating those 2 formulas, your new formula would look as follows:
=MIN(GcolDate,H1,IcolDate)
Note: the 2 variables already reduce by the 18months, so no other
subtraction in your formula is necessary.

Hope this helps.

 

[Elizabeth]

Wow! That is ugly. How do you know all this stuff?!

Okay, so you were right I do have the Anaylsis ToolPak. Now I have to figure
out how to use the EDATE thing right? Like this?
=MIN(EDATE(G2,-18),H2,EDATE(I2,-18))

This appears to be working beautifully! Thank you (and Mike too) for all the
help this afternoon!

 

[John C]

Yes, that is the correct format for the EDATE function inside your formula.
How do I know all this stuff? I don't know half as much as many other people.
I can only speak for myself when I say that when I see someone posing a
question, 99 times out of 100 I have already had to, or attempted to, do
something similar.

The EDATE function is a blessing.