Hi J

Well if you always assume that the End Time is always after the Start Time, you could had a day to the End Time if it's smaller than the Start Time. It will look like this:
SELECT
  Table1.time_field1 AS [Start Time],
  IIF(Table1.time_field2 < Table1.time_field1, Table1.time_field2 + TimeSerial(24,0,0),Table1.time_field2) AS [End Time],
  Table1.rate_per_hour,
  DateDiff('s',[time_field1],[End Time])/3600 AS TimeDifference
  DateDiff('s',[time_field1],[End Time])/3600 * [rate_per_hour] AS Payroll
FROM Table1;

This solution will correct your last problem, but what will you do with this case:
timefield1START timefield2END timeDifference
18:51 18:51 24:00 (the next day)
18:51 18:51 00:00 (the same day)

Stéphane Lajoie

J

If you need to include days in your calculations then include them in your data - it will save you soooooo much trouble in the long run.

I made this mistake in an engineers working time logging database. Two years and many pages of data validation code later, I'm still not convinced that I'm always getting the correct data.


Brian Skelton
Braxis Computer Services Ltd.

Hi J,

Stephane and Brian probably have the final word for you, but...well...I was playing around and here is the "function call from a query" version, just for kicks:

This goes in a standard module and does a bunch of conversions:

Function ConvertToDouble(TimeDifference)
    TimeDifference = CDate(TimeDifference)
    ConvertToDouble = CDbl(TimeDifference) * 24
End Function

Here's the SQL:

SELECT
    Table1.time_field1 AS [Start Time],
    Table1.time_field2 AS [End Time],
    ConvertToDouble(Format([time_field1]-1-[time_field2],"Short
         Time")) AS [Time Difference]
FROM Table1;

Two business rules:
   a) no one works over 24 hours without clocking out (seems
      reasonable)
   b) all hours are recorded in 15 minute intervals (or else
      format the return value to the number of decimals of your
      choice)

Seems to work. Heres some data:

Start Time End Time Time Difference
18:45 00:45 6
06:00 12:00 6
04:30 13:00 8.5
19:00 00:45 5.75
12:00 15:00 3
20:00 01:30 5.5
17:15 18:15 1

Bob

I'm sure you all have this working by now, but while looking for something else, I ran across this KB Article:
http://support.microsoft.com/default...b;en-us;210604

Towards the bottom is discusses TimeCard calculations. Just an FYI.

Beth

Now you're losing me. The time difference 1:30 means "one hour and thirty minutes." To translate that into hours is not 1.3 hours; it is 1.5 hours. So 1:30 should not be converting to 1.3 because 1.3 is approximately one hour and twenty minutes.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I realize this is an old thread, but thanks to those with the answers. This one was simple and great help.

Hey,

The solution:

(24-([Formulieren]![perso-uren-overzicht form]![beginuur]*24))+(0+([Formulieren]![perso-uren-overzicht form]![einduur]*24))

I now is in Dutch but:

Change 'formulier to Forms'
and [perso-uren-overzicht form]![beginuur]* [to your fieldname]

This give the exact respons

Hope you can use this one

grds

by the way:
beginuur = start-time
einduur = end-time