DATE function
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
When using this to get the next month, find some problem when at end of a long month (with 31 days), the result came back is the first day of 2 months later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected 30/4/2008. Any way to make it consistant to display end of month Thanks |
DATE function
With the Analysis ToolPak Add-In loaded...
=EOMONTH(A1,1) Rick "Learn-more" wrote in message ... =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) When using this to get the next month, find some problem when at end of a long month (with 31 days), the result came back is the first day of 2 months later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected 30/4/2008. Any way to make it consistant to display end of month Thanks |
DATE function
On Sun, 4 May 2008 23:14:00 -0700, Learn-more
wrote: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) When using this to get the next month, find some problem when at end of a long month (with 31 days), the result came back is the first day of 2 months later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected 30/4/2008. Any way to make it consistant to display end of month Thanks Rick gave you the answer if you have the Analysis ToolPak installed (or with Excel 2007). If you don't have that installed, you can use this formula: =MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1})) Note that the {2,0} and {0,1} factors are both array-constants (enclosed in braces). But the formula is entered normally. --ron |
DATE function
Rick gave you the answer if you have the Analysis ToolPak installed (or
with Excel 2007). If you don't have that installed, you can use this formula: =MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1})) Note that the {2,0} and {0,1} factors are both array-constants (enclosed in braces). But the formula is entered normally. Actually, in looking at it, I may not have given the OP what he/she wanted. The EOMONTH function I proposed gives back the last day of the next month no matter what day in the specified month it is... your function gives the same date in the next month unless that date would carry you into the month after that (whereby it adjusts the result back to the last day of the correct month). In re-reading the OP's post, I kind of think your solution is the one he/she is after. Rick |
DATE function
On Mon, 5 May 2008 10:00:45 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: Rick gave you the answer if you have the Analysis ToolPak installed (or with Excel 2007). If you don't have that installed, you can use this formula: =MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1})) Note that the {2,0} and {0,1} factors are both array-constants (enclosed in braces). But the formula is entered normally. Actually, in looking at it, I may not have given the OP what he/she wanted. The EOMONTH function I proposed gives back the last day of the next month no matter what day in the specified month it is... your function gives the same date in the next month unless that date would carry you into the month after that (whereby it adjusts the result back to the last day of the correct month). In re-reading the OP's post, I kind of think your solution is the one he/she is after. Rick I think you're correct, but with the ATP installed, he could also use the EDATE function, which is probably what you meant to type, and what I "saw" :-( --ron |
DATE function
Got the answer. Thanks all.
"Learn-more" wrote: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) When using this to get the next month, find some problem when at end of a long month (with 31 days), the result came back is the first day of 2 months later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected 30/4/2008. Any way to make it consistant to display end of month Thanks |
All times are GMT +1. The time now is 06:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com