Results 1 to 6 of 6

Thread: Year End (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Year End (A2k)

    The following expression returns the first date of this year:

    =DateAdd("y",1-DatePart("y",Date()),Date())

    I am at a loss with the last date of this year.
    Can some one help with the expression required.
    Thanks

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year End (A2k)

    What about

    DateSerial(Year(Date()),Month(Date())+(12-Month(Date())+1),0)

    Tom

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Year End (A2k)

    Take the result of that calculation and use dateadd to first add one year and then subtract one day.

    Wait a minute. Why do you need to do all that to calculate the first of the year? CDate("1/1/" & Year(date())) will give you the same thing. and CDate("1/1/" & Year(date()) + 1) -1 will give you the last day of this year.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year End (A2k)

    Thanks Tom & Charlotte.
    I've got it now.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Year End (A2k)

    Why can't you just use CDate("31/12/" & Year(date())) or CDate("12/31/" & Year(date())) ?

    I am not sure how Cdate relates to local date settings.
    Both work for me, and display as 31/12/2003 (which I what I would want).
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Year End (A2k)

    CDate merely converts a date string into a datetime value in VBA. It uses the regional settings to recognize and handle a date string.
    Charlotte

Posting Permissions

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