Consulting

Results 1 to 5 of 5

Thread: Get first and last dates (dd/mm/jj) from year range

  1. #1
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location

    Get first and last dates (dd/mm/jj) from year range

    Hi Folks

    This seems to be pretty straight forward, but my brain doesn't do anymore.

    So if the user wants to plan for 2020, i'd like the date range to start from first week 2019 (i.e. starting from monday that is 31.12.18) and the last week of 2021 (i.e. 31.12.21) and then fill up the dates in between.

    It sounds easy, but turns out as a nightmare.

    any ideas?

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I've seen more complicated ways to do it, but I prefer the more straight-forward way below


    Option Explicit
    
    
    Sub ThreeYears()
        Dim dateStart As Date, dateEnd As Date
        Dim i As Long, o As Long
    
    
        dateStart = DateSerial(Year(Now), 1, 0)
        Do While Weekday(dateStart) <> vbMonday
            dateStart = dateStart - 1
        Loop
    
    
        dateEnd = DateSerial(Year(Now) + 3, 1, 0)
    '    MsgBox dateStart & " -- " & dateEnd
    
    
        o = 0
        For i = dateStart To dateEnd
            ActiveSheet.Cells(o + 1, 1).Value = dateStart + o
            o = o + 1
        Next i
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
       sn = [index(date(2019,1,4)-weekday(date(2019,1,4),2)+row(offset(A1,,,date(2021,1,4)-weekday(date(2021,1,4),2)-3-(date(2019,1,4)-weekday(date(2019,1,4),2)))),)]
       sheet1.cells(1).resize(Ubound(sn))=application.transpose(sn)
    End Sub

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by snb View Post
    Sub M_snb()
       sn = [index(date(2019,1,4)-weekday(date(2019,1,4),2)+row(offset(A1,,,date(2021,1,4)-weekday(date(2021,1,4),2)-3-(date(2019,1,4)-weekday(date(2019,1,4),2)))),)]
       sheet1.cells(1).resize(Ubound(sn))=application.transpose(sn)
    End Sub
    For some reason, all I get in A1:A732 is 43465, which if I format it is 12/31/2018
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Well it proves that VBA is very consistent.

    You'd better use:

    Sub M_snb()
       sn = [index(date(2019,1,4)-weekday(date(2019,1,4),2)+row(offset(A1,,,date(2021,1,4)-weekday(date(2021,1,4),2)-3-(date(2019,1,4)-weekday(date(2019,1,4),2)))),)]
       Sheet1.Cells(1).Resize(UBound(sn)) = sn
    End Sub
    Alternative:

    Sub M_snb()
      x = DateSerial(2019, 1, 4)
      y = DateSerial(2021, 1, 4)
        
      Cells(1, 4) = x - Weekday(x, 2) + 1
      Cells(1, 4).AutoFill Cells(1, 4).Resize(y - Weekday(y, 2) - 2 - Cells(1, 4))
    End Sub
    Last edited by snb; 11-19-2019 at 07:45 AM.

Posting Permissions

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