Can anyone help with the formula whereby if date entered in cell A1 formatted dd/mm/yy e.g. 31/10/06, then subsequent cells A2,A3,A4 auto full with the next month e.g. 30/11/06,31/12/06 and 31/01/06 (it has to be formatted this way)
Thanks
Can anyone help with the formula whereby if date entered in cell A1 formatted dd/mm/yy e.g. 31/10/06, then subsequent cells A2,A3,A4 auto full with the next month e.g. 30/11/06,31/12/06 and 31/01/06 (it has to be formatted this way)
Thanks
OK so I soon figured out that formatting things in european dates is a bear. I am not sure if this helps much, but if you are looking for just doing what you are saying this gets you around excels date calculation shortcomings, however you will not be able to calculate off of the displayed cell.
In cell A1 I have "30/11/06"
In cell B1 I have "=IF(DAY(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1))<10,"0"&DAY(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1)),DAY(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1))) &"/"& IF(MONTH(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1))<10,"0"&MONTH(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1)),MONTH(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1))) &"/" & RIGHT(YEAR(EOMONTH(DATE(VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT(A1,5),2)),LEFT(A1,2)),1)),2)"
Then you can just copy that on and it will keep going for the end of month factoring in all of the confusions of working with euro dates. As an aside, I have most the add-ins installed so i think EOMONTH may need add ins installed to function.
Sorry for the complexity, hope that helps
This maybe a little easier to understand and I think it should still work for Euro date formatting.
Put this in A2 and copy down
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Thanks for the replies - I think that jtb's solution would perhaps be the most straightforward - However, the only slight problem is that the formula doesnt quite return correct e.g. enter 31/10/06 in cell A1 and cell A2 returns 01/12/06 instead of 30/11/06 - is there something to add to the formula or am I doing something wrong?
Hi Martins,
It's working correctly. The month of Oct has 31 days. If you add 31 days to 31st Oct you get 01/12/06.
This formula will work apart from when it's a leap year
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+CHOOSE(MONTH(A1),28,31,30,31,30,31,31,30,31,30,31,31))
For leap years change 28 to 29
VBA Noob
Works fine - thanks very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks