Time Calculation Adjustment

[Guest]

Hello:

I have a simple little Time & Attendance application for tracking hours at
work. I have a form based calculation that works properly, however, I want to
subtract 1/2 hour [30 minutes] from the total every day because that's the
employee's lunch time. I tried various little things such as -.50 and none of
them work. I'm sure there's a very simple answer.

Here's the current expression:

=HoursAndMinutes([End_Time]-[Start_Time])

Thanks for your help,
Robert

 

[Douglas J Steele]

You're probably not going to like this answer, but you're using the Date
field inappropriately.

There is no Time data type in Access. While the Date data type holds Time
values, it's really only intended to be used for timestamps: values that
have both a Date and Time. That's because under the covers, Date values are
stored as 8 byte floating point numbers where the integer portion represents
the date as the number of days relative to 30 Dec, 1899 and the decimal
portion represents the time as a fraction of a day. If you use the Format
function on your fields, with an explicit format argument, you'll see what I
mean:

?Format(#08:45#, "yyyy-mm-dd hh:nn:ss")
1899-12-30 08:45:00

The problem you're going to run into is that if you add times together,
anything over 24 hours gets converted to days. In other words, #08:30# +
#06:30# + #07:00# + #07:30# will give you #05:30#, not #29:30#.

The normal recommendation is to store the duration as an integer in whatever
units you need (i.e: minutes if that's sufficiently resolution, seconds
otherwise).

If you're determined to use Date values, you'll need to subtract 0.020833333
to represent a half hour. (1/48)

 

[Guest]

Hello Douglas:

Thanks for the response, however, you lost me. I am used to a database
program called Alpha Five, where there are separate fields for dates and
time. Time is stored in it's own field, therefore, subtracting 30 minutes
from an employee's total hours is a piece of cake. I'm somehwat confused by
the Access combination of Date and Time in one field, I really don't
understand why it's done like that.

Anyway, back to my situation. Employees don't work past midnight, so that's
never an issue. And I only want to track an emplloyee's hours for each day
and then simply subtract 30 minutes from the total.

Unless I'm missing something in your explanation, it appears as if I'm going
to have to create my own NUMERIC fields for hours and minutes, and then do
the appropriate calculations. Would you agree with that?

Thanks,
Robert

 

[Douglas J. Steele]

Well, I did I say "If you're determined to use Date values, you'll need to
subtract 0.020833333 to represent a half hour. (1/48)"

I have no idea what the HoursAndMinutes function in your original post is,
but you could try:

=HoursAndMinutes([End_Time]-[Start_Time]-0.020833333)

or

=HoursAndMinutes([End_Time]-[Start_Time]-(1#/48#))

The # after the 1 and the 48 is to force them to be Doubles, as opposed to
Integers, for more accuracy:

?1/48
2.083333E-02
?1#/48#
2.08333333333333E-02

 

[Luke Dalessandro]

DateDiff("n", date2, date1) returns the number of minutes between two
dates...

This should allow you to do what you want to, as far as subtracting 30
minutes and then converting back to hours and minutes (using Mod 60).

Luke

 

[Douglas J. Steele]

For that matter, DateAdd("n", -30, MyTime) will subtract 30 minutes, so I
could have used

=HoursAndMinutes(DateAdd("n", -30, [End_Time]-[Start_Time]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

DateDiff("n", date2, date1) returns the number of minutes between two
dates...

This should allow you to do what you want to, as far as subtracting 30
minutes and then converting back to hours and minutes (using Mod 60).

Luke

Well, I did I say "If you're determined to use Date values, you'll need
to subtract 0.020833333 to represent a half hour. (1/48)"

I have no idea what the HoursAndMinutes function in your original post
is, but you could try:

=HoursAndMinutes([End_Time]-[Start_Time]-0.020833333)

or

=HoursAndMinutes([End_Time]-[Start_Time]-(1#/48#))

The # after the 1 and the 48 is to force them to be Doubles, as opposed
to Integers, for more accuracy:

?1/48
2.083333E-02
?1#/48#
2.08333333333333E-02

 

[Luke Dalessandro]

Lol, yeah

 

[Guest]

Hey, you guys are a great. I've had fun reading your replies. Unfortunately,
I don't have access [so to speak] to my database this afternoon, however, I'm
going to try your suggestions as soon as I can.

I'm also thinking of playing around with my own separate numeric fields for
hours and minutes to see how that works with Access.

Thanks for the interesting thoughts and comments,
Robert