Min Values minus 18 months

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Elizabeth - 31 Jul 2008 17:20 GMT

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

Reply to this Message

Mike H - 31 Jul 2008 17:30 GMT

Try

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

Mike

> 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

Reply to this Message

Elizabeth - 31 Jul 2008 17:47 GMT

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

> Try
>
[quoted text clipped - 8 lines]
> >
> > Thank you. Elizabeth

Reply to this Message

Mike H - 31 Jul 2008 18:28 GMT

Maybe

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

Mike

> I tried this formula but it comes up with 04/02/07 but shouldn't it be
> 02/28/07?
[quoted text clipped - 18 lines]
> > >
> > > Thank you. Elizabeth

Reply to this Message

Elizabeth - 31 Jul 2008 18:52 GMT

Wouldn't that also subtract 18 months from H1?

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

> Maybe
>
[quoted text clipped - 24 lines]
> > > >
> > > > Thank you. Elizabeth

Reply to this Message

Mike H - 31 Jul 2008 19:25 GMT

Hmmm

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

Mike

> Maybe
>
[quoted text clipped - 24 lines]
> > > >
> > > > Thank you. Elizabeth

Reply to this Message

Elizabeth - 31 Jul 2008 19:29 GMT

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

> Hmmm
>
[quoted text clipped - 30 lines]
> > > > >
> > > > > Thank you. Elizabeth

Reply to this Message

David Biddulph - 31 Jul 2008 19:46 GMT

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

Signature

David Biddulph

> This is kinda funny because that is exactly the answer I'm at with the
> other
[quoted text clipped - 49 lines]
>> > > > >
>> > > > > Thank you. Elizabeth

Reply to this Message

John C - 31 Jul 2008 17:31 GMT

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.

Signature

John C

> 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

Reply to this Message

Elizabeth - 31 Jul 2008 17:53 GMT

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

> Two ways. Assuming the base date is in cell D3
>
[quoted text clipped - 11 lines]
> >
> > Thank you. Elizabeth

Reply to this Message

Elizabeth - 31 Jul 2008 18:01 GMT

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

> 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
[quoted text clipped - 20 lines]
> > >
> > > Thank you. Elizabeth

Reply to this Message

John C - 31 Jul 2008 19:40 GMT

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.

Signature

John C

> Actually it appears to be off by one day in some cases but correct in most of
> them Is that because of leap year?
[quoted text clipped - 29 lines]
> > > >
> > > > Thank you. Elizabeth

Reply to this Message

Elizabeth - 31 Jul 2008 20:02 GMT

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!

> 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
[quoted text clipped - 41 lines]
> > > > > =MIN(G1-547.5,H1,I1-547.5)
> > > > > Thank you. Elizabeth

Reply to this Message

John C - 31 Jul 2008 20:44 GMT

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.

Signature

John C

> Wow! That is ugly. How do you know all this stuff?!
>
[quoted text clipped - 50 lines]
> > > > > > =MIN(G1-547.5,H1,I1-547.5)
> > > > > > Thank you. Elizabeth

Reply to this Message