Total Working Hours

[cathalfarrell]

Hi ther,

I wonder if someone could help me with what I am sure is simply code, I just can't seem to get the response I want.

I am working out each member of staffs working hours for the day.

[Start Time]
[Finish Time]
minus a 30 min lunch.

I would like to display the total hours worked for the day in an hour:minute format.

I've tried a few diferent things and still not got the answer I was after.

Your help with this is greatly appreciated.

 

[namliam]

This will do just that
[finish time] - [start time] - 30/60/24

Regards & GL

 

[DCrake]

Simple Software Solutions

When calculating between two times you need to bear in mind the following:

What happens if the times cross midnight
Minimum hours worked to qualify for a 30 min lunch break

First step work out the number of mins between the 2 date/times

Mins = DateDiff("n",Start,End)

Next convert to Hours and mins

MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")

Example:

Create a function named HoursWorked

Public Function HoursWorked(DteLower as Date,DteUpper As Date) As String

Dim Mins As Integer

Mins = DateDiff("n",DteLower,DteUpper)


'Deduct Lunch break if Rules say so
If Mins > 30 Then Mins = Mins -30


'Calculate duration and display as h Hours and n minutes

HoursWorked = = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")

End Function

CodeMaster:http://www.icraftlimited.co.uk

 

[namliam]

This will do just that
[finish time] - [start time] - 30/60/24

Regards & GL

To allow for finish times past midnight but no more than 24 hours...

iif([finish time] < [start time], 1, 0) + [finish time] - [start time] - 30/60/24

btw:
Using spaces in field names is generaly not a good idea...

 

[DCrake]

Simple Software Solutions

Forgive my ignorance but I cannot understand the following

[finish time] - [start time] - 30/60/24

Whats does this do in the context of calculating the elapsed time

 

[Mile-O]

-30 minutes, to remove the half hour for lunch
divided by 60, for minutes in an hour
divided by 24, for hours in a day.

 

[namliam]

Like Mile-O said.... but let me explain a bit more detail

A date in actuality is a double, this double is split into two parts the Integer and the decimal.

Lets take an example:
March 11, 2008 10:48 am. This is the double 39518.45
39518 means there have been 39518 days since Jan 1st 1900.
.45 means that .45 of one day has passed. Since one day has 24 hours, that is .45 * 24 = 10.8 So 10 hours have passed.
.8 hours, an hour has 60 minutes, meaning .8 * 60 = 48 minutes

Now reverse this, we want to remove 30 minutes from any given time. Knowing we can use "regular math"....
- 30 (minutes) / 60 (minutes in an hour) / 24 (hours in a day) = 0.020833333 = 30 minutes

Same way you could substract 30 seconds...
- 30 / 60 / 60 / 24

Offcourse adding the 60 seconds to devide by.

Or offcourse you could make funtions like the example given earlier by you DCDrake,
or use the DateAdd function: Dateadd( "n", -30, iif([finish time] < [start time], 1, 0) + [finish time] - [start time] )
or use the Timevalue function: iif([finish time] < [start time], 1, 0) + [finish time] - [start time] - timevalue("00:30:00")

There are offcourse wholes in this, but should work for anything < 24 hour "worktime"

P.S. to check if I am right ... You can execute below in a debug window
?(timevalue("10:48:00"))
10:48:00 AM
?cdbl(timevalue("10:48:00"))
0.45

 

[cathalfarrell]

Thanks for you help

thanks everyone for your assistance and explainations to help improve my understanding.

The [finish time] - [start time] - 30/60/24

worked a treat as staff will never be working past midnight or over two days, well not at the moment anayway!

Thanks again All!