Time calculation instructions

B

blink

Hello,
Would someone be so kind as to put up instructions on calculating time.
Basically looking for: Field2 subtracted from Field1 and output Field3.
Should this be in a query? Thanks in advance.
 
D

Duane Hookom

Date and Time fields are basically numbers. You could calculate in a query
with a field expression like:
Field3: Field1 - Field2
The Field3 value will be expressed in the number of days. For instance 0.25
is 1/4th day or 6 hours.

I prefer using the DateDiff() function since it allows me to choose a
date/time interval such as hours, minutes, seconds, weeks,..
 
J

John Vinson

Hello,
Would someone be so kind as to put up instructions on calculating time.
Basically looking for: Field2 subtracted from Field1 and output Field3.
Should this be in a query? Thanks in advance.
Click to expand...

Yes, it should be in a Query.

The time difference should NOT EXIST in any table.

If you have a table containing date/time fields named Field1 and
Field2 (which should obviously be changed to something meaningful!),
then create a new Query based on the table. Select Field1 and Field2
(if you want to see them, it's not essential); and in a vacant Field
cell at the top row of the query grid type

Field3: DateDiff("n", [Field1], [Field2])

If Field1 contains #10:00am# and Field2 contains #11:30am# then Field3
will contain an integer 90, the number of minutes between those two
times.

John W. Vinson[MVP]
 
R

Rob

John said:
.....

If you have a table containing date/time fields named Field1 and
Field2 (which should obviously be changed to something meaningful!),
then create a new Query based on the table. Select Field1 and Field2
(if you want to see them, it's not essential); and in a vacant Field
cell at the top row of the query grid type

Field3: DateDiff("n", [Field1], [Field2])

If Field1 contains #10:00am# and Field2 contains #11:30am# then Field3
will contain an integer 90, the number of minutes between those two
times.

John W. Vinson[MVP]
Click to expand...

Taking this further, please take a look at this Expression:

Field1 = [StartTime] = time I enter my office
Field2 = [EndTime] = time I leave office and go home
Field3 = [StandardTime] = time I am supposed to be in the office ( 8.5
hours or 08:30 )

Day: [EndTime] - [StartTime] - [StandardTime]

Entering office at 8AM and leaving at 5PM, I have been present 09:00
which is plus 00:30
Entering at 8AM and leaving at 4PM, I have been present 08:00 which is
minus 00:30

In a Query Expression however, both examples result in a positive 00:30
(absolute value)

Not going to work at all, gives a + 42:30 each week. That is an easy way
to make a living.

What can I do? Would like to Sum workdays each week, changing formatting
won't work.

Rob
 
J

John Vinson

Taking this further, please take a look at this Expression:

Field1 = [StartTime] = time I enter my office
Field2 = [EndTime] = time I leave office and go home
Field3 = [StandardTime] = time I am supposed to be in the office ( 8.5
hours or 08:30 )
Click to expand...

That's your mistake.

DateDiff DOES NOT RETURN A DATE/TIME VALUE such as 8:30. It returns an
integer number of minutes.

DateDiff("n", [StartTime], [EndTime])

will return 510 if you put in 8:00 and 4:30.

If you make your StandardTime an integer number of expected minutes,
you can use

Day: DateDiff("n", [StartTime], [EndTime]) - [StandardTime]

to calculate the number of minutes over or under the expected time.

Subtracting date/time values will actually (sort of) work - it will
give its result in Double Float days (or fractions of a day). But the
DateDiff functions are really much easier to manage.

John W. Vinson[MVP]
 
Top