Find last day of the week with a user inputted date VBA

Question

I'm trying to set up a system such that if a start and end date falls between two separate weeks, I want to find the last day of the week first week (friday in this case) and then input to a cell so that the first date to the last day of the week are in the same row and create a next cell that will go from the ldotw to either the next ldotw or the end date.

My code so far is:

With Sheets(p)
        If WorksheetFunction.WeekNum(startdate, 15) <> _         WorksheetFunction.WeekNum(enddate, 15) Then
            ldotw = UserForm1.startdate.Value - _ 
Weekday(UserForm1.startdate.Value, vbFriday) + 7
            While Not equal
                    .Cells(oneRow, 6) = WorksheetFunction.WeekNum(startdate, 15)
                    .Cells(oneRow, 7) = UserForm1.startdate.Text
                    .Cells(oneRow, 9) = ldotw
                'makes sure to get the last week of values
                If WorksheetFunction.WeekNum(startdate, 15) = _ WorksheetFunction.WeekNum(enddate, 15) Then
                    equal = True
                End If
                'sets up variables for the next week
                oneRow = oneRow + 1
                UserForm1.startdate.Text = ldotw
                ldotw = ldotw + 7
                If WorksheetFunction.WeekNum(startdate, 15) = WorksheetFunction.WeekNum(enddate, 15) Then
                    ldotw = UserForm1.enddate.Value
                End If
            Wend
        ElseIf WorksheetFunction.WeekNum(startdate, 15) = _ WorksheetFunction.WeekNum(enddate, 15) Then
                    .Cells(oneRow, 7) = UserForm1.startdate.Text
                    .Cells(oneRow, 9) = ldotw
                        hours = .Cells(oneRow, 9) - .Cells(oneRow, 7)
        End If
End With

An error occurs on ldotw = UserForm1.startdate.Value - Weekday(UserForm1.startdate.Value, vbFriday) + 7.

I put a 'MsgBox (Format(Date, (Weekday(sDate, vbFriday) + 7))' and i made the day 6/2/15 and it only returned 12. Rather than 6/5/15 so i deleted the +7 so that should return 5. But i would want ldotw to be 6/5/15 12 AM — Tim Swingle
I've done some more debugging and I got to 'ldotw = sDate + 8 - Weekday(sDate, vbFriday)' which will return to friday. but it will keep the time at whatever time you had submitted via the form. any ideas on how to make the hours and minutes go to 00:00 (12 AM) — Tim Swingle
Dates are stored as type Double with the time part represented by the fractional component. So Int(DateVar), will get rid of the time component. — TnTinMn
If you add that as an answer i will say it worked! thanks!!!! — Tim Swingle

TnTinMn TnTinMn · Accepted Answer · 2015-06-18T20:41:42

The OP resolved the original issue himself, this answer is being posted per his request to a secondary question in the comments concerning eliminating the time component of a date.

I've done some more debugging and I got to 'ldotw = sDate + 8 - Weekday(sDate, vbFriday)' which will return to friday. but it will keep the time at whatever time you had submitted via the form. any ideas on how to make the hours and minutes go to 00:00 (12 AM)

Dates are stored as type Double with the time part represented by the fractional component. So Int(DateVar), will get rid of the time component.

Find last day of the week with a user inputted date VBA

1 Answers