+ Reply to Thread
Results 1 to 6 of 6

Auto Date Fill

  1. #1
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200

    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

  2. #2
    Registered User
    Join Date
    08-17-2004
    Posts
    7

    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

  3. #3
    Forum Contributor
    Join Date
    03-15-2005
    Location
    North Carolina
    MS-Off Ver
    2003 & 2007
    Posts
    180
    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))

  4. #4
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200

    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?

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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

  6. #6
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200

    date fill

    Works fine - thanks very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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