Sum Times

[Bob Quintal]

I have four fields on a form to show time. I want a seprate
"Total" field to add the time between the first two fields and
then add the time between the second two fields. Like this:

In LunchOut LunchIn
Out Total
6:00am 12:00pm 12:30pm
4:30pm 10 hours

The first four fields are stored as medium times. Can someone
let me know how to do this?

Tyhe datetime field type in access stores the information as the
number of days since 31 December 1899, with the time being a
fraction of a day. Assuming that all four fields having the same
day, it's a matter of subtracting the start from the stop for
each pair and adding the resulting durations.
The dateadd() function is a handy way of getting the result
expressed in years, months, days, hours, minutes

'the 'n' parameter returns miNutes, m would be Months
Result = Datediff("n",in,lunchout)+ Datediff("n",Lunchin,out)

To format the minutes into hours and minutes,
use integer division and the modulus to extract those values.
Total = Format(result \ 60,"00")&":"&Format(result mod 60,"00")

 

[ladybug via AccessMonster.com]

I have four fields on a form to show time. I want a seprate "Total" field to
add the time between the first two fields and then add the time between the
second two fields. Like this:

In LunchOut LunchIn Out
Total
6:00am 12:00pm 12:30pm 4:30pm
10 hours

The first four fields are stored as medium times. Can someone let me know
how to do this?

 

[John W. Vinson]

I have four fields on a form to show time. I want a seprate "Total" field to
add the time between the first two fields and then add the time between the
second two fields. Like this:

In LunchOut LunchIn Out
Total
6:00am 12:00pm 12:30pm 4:30pm
10 hours

The first four fields are stored as medium times. Can someone let me know
how to do this?

A couple of suggestions and clarifications:

These fields are NOT stored as "medium times". The format of a Date/Time field
*only* controls how the field is displayed, not what's stored in the table. A
Date/Time value is actually stored as a number, a count of days and fractions
of a day since midnight, December 30, 1899; so your 6:00am value (on all days,
for all records) is actually stored as 0.25 and is equivalent to #12/30/1899
06:00:00#. You may want to store the DATE AND TIME together in the same field
(I'm guessing that you're storing the workdate in a fifth field).

Secondly, you should not store the Total time in your table AT ALL. Storing
derived data such as this in your table accomplishes three things: it wastes
disk space; it wastes time (almost any calculation will be MUCH faster than a
disk fetch); and most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data in your table
WHICH IS WRONG, and no automatic way to detect that fact.

You can dynamically calculate the total time using the DateDiff function. To
calculate the number of minutes on the job use

DateDiff("n", [In], [LunchOut]) + DateDiff("n", [LunchIn], [Out])

This value can be displayed in hours:minutes form using an expression like

(DateDiff("n", [In], [LunchOut]) + DateDiff("n", [LunchIn], [Out])\60) &
Format(DateDiff("n", [In], [LunchOut]) + DateDiff("n", [LunchIn], [Out]) MOD
60, ":00")

This expression will return a Text string though - if you want to sum or
search or compare time-on-job values use the numeric value instead.

Note that (unless your business rules would prohibit it) there might be
additional absences; would it be possible for an employee to have a permitted
absence during the day, e.g. for a medical appointment or family emergency? If
so you might want to store each continuous work period in a separate record:

EmployeeID<link to employees table>
WorkStart <e.g. 7/10/2007 6:05am)
WorkEnd <e.g. 7/10/2007 12:10am)
EndReason <"Lunch">


John W. Vinson [MVP]

 

[SteveM]

Check the Access help for the DateDiff() function.

Steve