Time Sheets

I am working on an MS-Access time & billing program for consultants. This is an old program that was originally designed in Access 2.0, and over the years it was converted to 97 and then 2000, and finally was split into separate front end / back end files.

One of the new features requested is to allow the consultant to view their hours in comparison to the standard 8-hour day and 5-day week. For example, if they work 6 hours on Monday, then 10 on Tuesday, then 7 on Wednesday, the program should show 23 hours worked vs. the standard 24 hours (3 days x 8 hours) and indicate that they are 1 hour short. This required a few new functions so I'd like to share those.

First of all, I needed to filter the dates to the current week. For that I created two functions, fairly straightforward, as follows:

Function Get_StartOfWeek() As Date
Dim d As Date
d = Date
Do While DatePart("w", d, vbSunday) > 1
d = DateAdd("d", -1, d)
Loop
Get_StartOfWeek = d
End Function


Function Get_EndOfWeek() As Date
Dim d As Date
d = Date
Do While DatePart("w", d, vbSunday) < 7
d = DateAdd("d", 1, d)
Loop
Get_EndOfWeek = d
End Function

...and then the WHERE clause in the query looks like this:

WHERE [WorkDate]>=Get_StartOfWeek() And [WorkDate]<=Get_EndOfWeek()

The next step was a bit of a challenge, because the consultant might work on a Sunday or Saturday and those hours need to be included in the weekly totals, but not affect the "standard week" hour calculation. My solution was to use the Choose() function which then allowed me to set the daily expectations according to the day of the week. This is used inside a query, so I could not use the vbSunday constant, that's why the "1" is hard-coded into the DateDiff() function:

StandardWeekToDate:
8*Choose(DateDiff("d",Get_StartOfWeek(),[WorkDate],1),1,2,3,4,5,5)

You may ask, why are there only six items in the Choose arguments, when there are seven days in a week? The reason is that the Choose arguments are 1-based, so if the [WorkDate] is a Sunday, then DateDiff returns a zero and so Choose returns a Null. Which is fine in this particular scenario, because Sunday is not a normal workday.

If you need to load values for all seven days of the week, then you have to subtract 1 from the [WorkDate] in order to get a Sunday date to return item 1 from the Choose. You can't substitute vbSaturday (7) for the start of week in DateDiff; that would give the wrong answer. Here is the seven-day version:

StandardWeekToDate:
8*Choose(DateDiff("d",Get_StartOfWeek(),DateDiff("d",-1,[WorkDate]),1),0,1,2,3,4,5,5)