| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Listing values minus the zero amounts | Excel Worksheet Functions | |||
| a sum of #'s minus the lowest two values | Excel Discussion (Misc queries) | |||
| Date minus 3 months | Excel Worksheet Functions | |||
| ... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions | |||
| Importing values w/trailing minus signs | Excel Discussion (Misc queries) | |||
Hybrid Mode
