Timesheet Calculations (1 Viewer)

[Jgr4ng3]

Hello

I'm not great with VBA so I've been trying to figure out a way to do with with expressions and welcome your feedback!

I have a timesheet database, which has the exact start time and exact end time. I want to ignore this if it is five minutes either side of the half hour, and display the exact half hour, but otherwise want to round it UP to the nearest fifteen minutes.

For example:

Exact Start: 09:58:23 Displays as: 10:00:00
Exact Start: 09:42:16 Displays as: 09:45:00
Exact Start: 09:02:08 Displays as: 09:00:00
Exact Start: 09:07:01 Displays as: 09:15:00
Exact Start: 09:23:03 Displays as: 09:30:00
Exact Start: 09:33:00 Displays as: 09:30:00
Exact Start: 09:36:04 Displays as: 09:45:00

 

[CJ_London]

Just to be clear from your description - a time of

09:46:33 would be rounded up to 10:00:00
09:16:33 would be rounded up to 09:30:00

 

[CJ_London]

This function should do what you want - if you put it in a standard module you call call it from a query

Public Function RndTime(TimeVal As Date) As Date
Dim strTime As Integer
 
strTime = Format(TimeVal, "nnss")
 
Select Case strTime
    Case Is > 4500
        RndTime = CDate(IIf(Left(TimeVal, 2) = "23", "00", Val(Left(TimeVal, 2)) + 1) & ":00:00")
    Case Is > 3500
        RndTime = CDate(Left(TimeVal, 2) & ":45:00")
    Case Is > 1500
        RndTime = CDate(Left(TimeVal, 2) & ":30:00")
    Case Is > 500
        RndTime = CDate(Left(TimeVal, 2) & ":15:00")
    Case Else
        RndTime = CDate(Left(TimeVal, 2) & ":00:00")
End Select
 
End Function

 

[Jgr4ng3]

Looks great, thank you. How would I refer to this in the query?

Currently..

SELECT Timesheets.Agent, Timesheets.Date, Timesheets.[Exact Shift Start], Timesheets.[Exact Shift End], Timesheets.[Lunch Duration], Timesheets.[Lunch 2 Duration]
FROM Timesheets;

 

[CJ_London]

Just like this - just make sure the function is in a standard module

SELECT Timesheets.Agent, Timesheets.Date, Timesheets.[Exact Shift Start], Timesheets.[Exact Shift End], Timesheets.[Lunch Duration], Timesheets.[Lunch 2 Duration][B], RndTime(Timesheets.[Exact Shift Start]) as RndStart, RndTime(Timesheets.[Exact Shift End]) as RndEnd [/B]
FROM Timesheets

 

[Jgr4ng3]

Hello

I havent fully verified (Will be testing the data shortly) but seems to be perfect, thank you very much!

I am now creating a query based on this query that is a cross tab. SQL Below. I'm getting the Data Type Mismatch in Criteria Expression error however I have no criteria!

TRANSFORM Sum([Timesheets-PayrollCalculation].[Hours Worked]) AS [SumOfHours Worked]
SELECT [Timesheets-PayrollCalculation].Agent
FROM [Timesheets-PayrollCalculation]
GROUP BY [Timesheets-PayrollCalculation].Agent
PIVOT [Timesheets-PayrollCalculation].Date;

 

[CJ_London]

You are using a reserved word - Date - you'll need to rename this to something else

 

[Jgr4ng3]

You are using a reserved word - Date - you'll need to rename this to something else

Weird. Its fine in every other query.

Renamed to [ShiftDate] and still the same error.

 

[CJ_London]

Here is a list of reserved words - use of them incorrectly can result in unexpected behaviour

http://support.microsoft.com/kb/286335

You'll need to change it in your original table of not done already.

Access will optimise nested queries (i.e. a query based on another query) and effectively combines them together so your error may be being caused 'further down the line'.

Another possibility is you are missing some data and have a null or invalid value somewhere.

 

[Jgr4ng3]

Hello

It appears to be working now (there were some 'Error' or blank values).

Can you just explain what that function says for me so I know in future and can change it if I need to?

Thank you so much for your help!

 

[CJ_London]

The function converts a time into a number based on minutes and seconds so 45 min 10 secs =4510. I could have set it up so this was expressed in seconds so 45m 10 secs=2710 but this is not so 'readable'

A case statement tries each option until one is found that meets the criteria or until it 'drops off' the end

so

Select Case strTime
    Case Is > 4500 [COLOR=green]'greater than 45 mins
[/COLOR]        RndTime = CDate(IIf(Left(TimeVal, 2) = "23", "00", Val(Left(TimeVal, 2)) + 1) & ":00:00")
    Case Is > 3500 [COLOR=green]'greater than 35 mins
[/COLOR]        RndTime = CDate(Left(TimeVal, 2) & ":45:00")

If at some point you want to apply the 1/2 hr 'rule' to the 1/4 hrs you would change the above to

Select Case strTime
   [B] Case Is > 5000
[/B]        RndTime = CDate(IIf(Left(TimeVal, 2) = "23", "00", Val(Left(TimeVal, 2)) + 1) & ":00:00")
    Case Is > 3500
        RndTime = CDate(Left(TimeVal, 2) & ":45:00")
etc

The reason this is different

CDate(IIf(Left(TimeVal, 2) = "23", "00", Val(Left(TimeVal, 2)) + 1)

is because when rounding up to the hour, you need to allow for midnight otherwise the time would have become 24:00:00 which is not valid

 

[Jgr4ng3]

Perfect - thank you for your help!