GetWeekStartDate code won't work

[bailey11]

I have a database and I am trying to tell it that the workweek for calculating payroll is Sunday through Saturday.


I wrote the code below, but it won't calculate RT when the week starts over, it just keeps going as if the week didn't end.

Function GetWeekStartDate(DateWorked As Date) As Date
GetWeekStartDate = DateAdd("d", DatePart("w", DateWorked, vbSunday), DateWorked)
End Function

Function GetWeekEndDate(DateWorked As Date) As Date
GetWeekEndDate = DateAdd("d", 6, GetWeekStartDate(DateWorked))
End Function

Thanks in advance for any help.
Bailey11

 

[TheAceMan1]

How are ya bailey11 . . .

Is this all dependant on the Current Date?

See Ya! . . . . . .

 

[bailey11]

Yes.

 

[Eupher]

So the object, given the current date, is to find the first and last days of the current week? Try this:

GetWeekStartDate = Date() - WeekDay(Date()) + 1

And...

GetWeekEndDate = Date() - WeekDay(Date()) + 7

Both snippets assume Sunday is the first day of the week.

And here's a link to some more handy date functions:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210604

HTH,

Ken S.

 

[CaptainD]

Or using Eupher's code with a mod.

Function GetWeekStartDate(DateWorked As Date) As Date
    
    GetWeekStartDate = DateWorked - Weekday(DateWorked) + 1
    MsgBox GetWeekStartDate
End Function

Function GetWeekEndDate(DateWorked As Date) As Date
       GetWeekStartDate = DateWorked - Weekday(DateWorked) + 7
End Function

 

[bailey11]

I stated it wrong. It is not the current date, it is the date entered on the time sheet. For instance the time sheet starts calculating overtime when an employee hits forty hours. The timekeeper may be entering time for last week's payroll today and it needs to look at what has accumulated for last week to see if the hours are over time yet as eac day for last week is entered.

 

[Eupher]

bailey11,

It is not the current date, it is the date entered on the time sheet.

In that case, CaptainD's mod should work.

Ken S.