# Auto Date Fill

1. ## Auto Date Fill

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  Register To Reply

2. ## Formatting Date Cells

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  Register To Reply

3. 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))  Register To Reply

4. ## Date Fill

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?  Register To Reply

5. 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  Register To Reply

6. ## date fill

Works fine - thanks very much  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1