Min Values minus 18 months
 

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

Min Values minus 18 months
 

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

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.
--
John C


"Elizabeth" wrote:

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

Min Values minus 18 months
 

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))

"Mike H" wrote:

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

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)))


"John C" wrote:

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.
--
John C


"Elizabeth" wrote:

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

Min Values minus 18 months
 

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)))

"Elizabeth" wrote:

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)))


"John C" wrote:

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.
--
John C


"Elizabeth" wrote:

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

Min Values minus 18 months
 

Maybe

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

Mike

"Elizabeth" wrote:

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))

"Mike H" wrote:

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

Wouldn't that also subtract 18 months from H1?

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

"Mike H" wrote:

Maybe

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

Mike

"Elizabeth" wrote:

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))

"Mike H" wrote:

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

Hmmm

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

Mike

"Mike H" wrote:

Maybe

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

Mike

"Elizabeth" wrote:

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))

"Mike H" wrote:

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

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)))


"Mike H" wrote:

Hmmm

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

Mike

"Mike H" wrote:

Maybe

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

Mike

"Elizabeth" wrote:

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))

"Mike H" wrote:

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

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(Y EAR($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(Y EAR($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.


--
John C


"Elizabeth" wrote:

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)))

"Elizabeth" wrote:

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)))


"John C" wrote:

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.
--
John C


"Elizabeth" wrote:

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

Min Values minus 18 months
 

Your problem is in deciding what you mean by 18 months before a particular
date.

18 months before 28/8/08 is 28/2/07
31/8/08 is 3 days later than 28/8/08, so 18 months before 31/8/08 is 3/3/07

18 months before 1/9/08 is 1/3/07
--
David Biddulph

"Elizabeth" wrote in message
...
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)))


"Mike H" wrote:

Hmmm

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

Mike

"Mike H" wrote:

Maybe

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

Mike

"Elizabeth" wrote:

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))

"Mike H" wrote:

Try

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

Mike

"Elizabeth" wrote:

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

Min Values minus 18 months
 

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" wrote:
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(Y EAR($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(Y EAR($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.
--
John C
"Elizabeth" wrote:
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)))
"Elizabeth" wrote:
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)))
"John C" wrote:
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.
--
John C
"Elizabeth" wrote:
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

Min Values minus 18 months
 

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.
--
John C


"Elizabeth" wrote:

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" wrote:
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(Y EAR($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(Y EAR($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.
--
John C
"Elizabeth" wrote:
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)))
"Elizabeth" wrote:
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)))
"John C" wrote:
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.
--
John C
"Elizabeth" wrote:
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