Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
I found this formula in one of the posts (Credit to Barry Houdini) and I was
wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
You need to change 7,6 to 11,10, and 12,11, and 13,12 respectively
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
The easy option is to add a number of days onto the date. However this will not be an exact number of months. The Function used can be modified to add a number of months, I can do this for you but need to know what you would do if x months in the future the date did not exist, for example 31st february can not exist, would you be hapy for this to be the 1st of the next month? Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=506773 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
You need to change not just the 6, but the pair {7,6}. For n months ahead,
the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
Thank you Bob and bpeltzer. I searched some more and found the following and
was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
IT is hard to say which is better as we haven't been told what you are
trying to do, but I like Barry's solution as it is a neat way to forward you six months, going back to the last day in the month if that resultant month has fewer days than that month, so winding 31st March forward 6 months returns 20th September. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
The alternative, =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0), doesn't retain the day
of the start date. The final argument to the date function is the day; A day of 0 will result in the last day of the prior month, which is why you'd have to add 1+C9 months. So if you just need any date in the month that's six months ahead, this would work fine. If you want the date six months ahead, the other is more exact. --Bruce "JNW" wrote: Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
Thank you again for both of your help.
One more question. How would I use the same formula to subract 6 months from today? I tried substituting the + for a - but that didn't work. Any thoughts? "Bob Phillips" wrote: IT is hard to say which is better as we haven't been told what you are trying to do, but I like Barry's solution as it is a neat way to forward you six months, going back to the last day in the month if that resultant month has fewer days than that month, so winding 31st March forward 6 months returns 20th September. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
You also need to adjust the deductions
=MIN(DATE(YEAR(A2),MONTH(A2)-{6,5},DAY(A2)*{1,0})) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you again for both of your help. One more question. How would I use the same formula to subract 6 months from today? I tried substituting the + for a - but that didn't work. Any thoughts? "Bob Phillips" wrote: IT is hard to say which is better as we haven't been told what you are trying to do, but I like Barry's solution as it is a neat way to forward you six months, going back to the last day in the month if that resultant month has fewer days than that month, so winding 31st March forward 6 months returns 20th September. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
I did this for returning a date 10 months prior to the date in cell X2. I
entered the formula as follows: =MIN(DATE(YEAR(X2),MONTH(X2)-{10,9},DAY(X2)*{0,1})) The date in X2 is 2/2/06 and the formula returned 3/31/05 when it should return 4/2/05. Any ideas why? "Bob Phillips" wrote: You also need to adjust the deductions =MIN(DATE(YEAR(A2),MONTH(A2)-{6,5},DAY(A2)*{1,0})) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you again for both of your help. One more question. How would I use the same formula to subract 6 months from today? I tried substituting the + for a - but that didn't work. Any thoughts? "Bob Phillips" wrote: IT is hard to say which is better as we haven't been told what you are trying to do, but I like Barry's solution as it is a neat way to forward you six months, going back to the last day in the month if that resultant month has fewer days than that month, so winding 31st March forward 6 months returns 20th September. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
You reversed the 0,1 in my formula, it should be
=MIN(DATE(YEAR(X2),MONTH(X2)-{10,9},DAY(X2)*{1,0})) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... I did this for returning a date 10 months prior to the date in cell X2. I entered the formula as follows: =MIN(DATE(YEAR(X2),MONTH(X2)-{10,9},DAY(X2)*{0,1})) The date in X2 is 2/2/06 and the formula returned 3/31/05 when it should return 4/2/05. Any ideas why? "Bob Phillips" wrote: You also need to adjust the deductions =MIN(DATE(YEAR(A2),MONTH(A2)-{6,5},DAY(A2)*{1,0})) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you again for both of your help. One more question. How would I use the same formula to subract 6 months from today? I tried substituting the + for a - but that didn't work. Any thoughts? "Bob Phillips" wrote: IT is hard to say which is better as we haven't been told what you are trying to do, but I like Barry's solution as it is a neat way to forward you six months, going back to the last day in the month if that resultant month has fewer days than that month, so winding 31st March forward 6 months returns 20th September. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) )'
Would you look at that! Thanks, Bob.
"Bob Phillips" wrote: You reversed the 0,1 in my formula, it should be =MIN(DATE(YEAR(X2),MONTH(X2)-{10,9},DAY(X2)*{1,0})) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... I did this for returning a date 10 months prior to the date in cell X2. I entered the formula as follows: =MIN(DATE(YEAR(X2),MONTH(X2)-{10,9},DAY(X2)*{0,1})) The date in X2 is 2/2/06 and the formula returned 3/31/05 when it should return 4/2/05. Any ideas why? "Bob Phillips" wrote: You also need to adjust the deductions =MIN(DATE(YEAR(A2),MONTH(A2)-{6,5},DAY(A2)*{1,0})) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you again for both of your help. One more question. How would I use the same formula to subract 6 months from today? I tried substituting the + for a - but that didn't work. Any thoughts? "Bob Phillips" wrote: IT is hard to say which is better as we haven't been told what you are trying to do, but I like Barry's solution as it is a neat way to forward you six months, going back to the last day in the month if that resultant month has fewer days than that month, so winding 31st March forward 6 months returns 20th September. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JNW" wrote in message ... Thank you Bob and bpeltzer. I searched some more and found the following and was wondering if there are any fundamental differences? Just curiousity on my part because what you have posted works great. =DATE(YEAR($D$2),MONTH($D$2)+1+C9,0) "bpeltzer" wrote: You need to change not just the 6, but the pair {7,6}. For n months ahead, the array should be {n+1,n}, so {14,13} to advance 13 months. "JNW" wrote: I found this formula in one of the posts (Credit to Barry Houdini) and I was wondering how I might edit it. Right now it calculates the date 6 months from today without passing into the next month. I need to edit this formula in order to add 10, 11, and 13 months to the date given in another cell. I tried changing the 6 to 13 but that didn't work. Any ideas? Thank you. JNW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|