Results 1 to 4 of 4

Thread: Get weekdays between two dates in specific columns

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Get weekdays between two dates in specific columns

    Hey! I have two dates that is always 7 days or less. And I need every each weekday to be in a specific cell. If the weekdays between the two dates is Friday, Saturday. It should be in two specific cells.



    The only code I have is below and its not working properly. Its not always showing every day and its not in specific cell. Could anyone help me? Would be extremely happy!!
    Code:
    Dim FirstDate AsDateDim LastDate AsDate
    Dim NextDate AsDate
    Dim DateOffset As Range
    Dim DateIter AsDate
    
    FirstDate = Source.Range("J"& cell.Row).Value
    LastDate = Source.Range("K"& cell.Row).Value
    Set DateOffset = Target.Range("K"& LastRow +1)
    
    For DateIter = FirstDate To LastDate
        DateOffset.Value = DateIter
        Set DateOffset = DateOffset.Offset(0,1)
    Next DateIter
    
                  EndWith
    
                EndIf
     
    Next cell

  2. #2
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get weekdays between two dates in specific columns

    Hi & welcome to the forum.

    I needed to replicate your setup on the same sheet, but you can easily change that by pointing your "source" and "Target" to different sheets.

    Code:
    Sub foo()
        Dim FirstDate As Date
        Dim LastDate As Date
        Dim DateIter As Date
        Dim cell As Long
        Dim source As Worksheet
        Dim Target As Worksheet
        Dim LastRow As Long
        
        Set source = Sheets("Sheet2")
        Set Target = Sheets("Sheet2")
        
        LastRow = source.Cells(source.Rows.Count, "I").End(xlUp).Row
        
        For cell = 2 To LastRow
            FirstDate = source.Range("I" & cell).Value
            LastDate = source.Range("J" & cell).Value
            For DateIter = FirstDate To LastDate
                Target.Range("K" & cell).Offset(0, DatePart("w", DateIter, vbMonday) - 1).Value = Format(DateIter, "ddd")
            Next DateIter
        Next cell
    End Sub
    The final result will look like that:


  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get weekdays between two dates in specific columns

    Quote Originally Posted by JustynaMK View Post
    Hi & welcome to the forum.

    I needed to replicate your setup on the same sheet, but you can easily change that by pointing your "source" and "Target" to different sheets.

    Code:
    Sub foo()
        Dim FirstDate As Date
        Dim LastDate As Date
        Dim DateIter As Date
        Dim cell As Long
        Dim source As Worksheet
        Dim Target As Worksheet
        Dim LastRow As Long
        
        Set source = Sheets("Sheet2")
        Set Target = Sheets("Sheet2")
        
        LastRow = source.Cells(source.Rows.Count, "I").End(xlUp).Row
        
        For cell = 2 To LastRow
            FirstDate = source.Range("I" & cell).Value
            LastDate = source.Range("J" & cell).Value
            For DateIter = FirstDate To LastDate
                Target.Range("K" & cell).Offset(0, DatePart("w", DateIter, vbMonday) - 1).Value = Format(DateIter, "ddd")
            Next DateIter
        Next cell
    End Sub
    The final result will look like that:

    Thanks and THANKS ALOT!
    It worked perfectly, thanks alot again!! =D

  4. #4
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get weekdays between two dates in specific columns

    glad I could help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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