First day of week Date

[Zirak]

Hi,
I have a table that shows the work days of employees and the hours they have worked.
I need to calculate the Over time for each employee per week.
However I also need to adjust my calculation based on pay periods.
In any case, I have grouped ny data based on the week number, however I need to see what the Date of the first day and last day of every week is.

Can any one help me?

 

[PGTurner]

I use this function for a similar purpose. You can play with offset & vbMonday, vbTuesday etc constants to adjust to your week. Make sure you do reality check to see that a weeks worth of dates returns what you think it should.

Function StartLastWeek(SomeDate As Date)
    ' Any time from > midnight Monday to < midnight Monday
    '  will date to one second past midnight the previous Monday night.
    Dim OneSecond As Double
    Dim Offset As Integer
    OneSecond = (#12:01:02 AM# - #12:01:01 AM#)
    Offset = 1
    StartLastWeek = SomeDate - Weekday(SomeDate, vbMonday) + Offset
    StartLastWeek = Int(StartLastWeek) + OneSecond
End Function

Call it like:

?"First: " & StartLastWeek(MyDate) & _
  " Last: " & StartLastWeek(MyDate)+ 6