Time calculation instructions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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,..
 
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]
 
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
 
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]
 
Duane said:
Date and Time fields are basically numbers.
Click to expand...

Sure thing, that's why

? vbTuesday - vbMonday = vbSunday

returns True <tongue firmly embedded>.

Serious now, an alternative approach is that date values are from the
domain of, well, date values and thus should always be operated on
using temporal functions.

The SQL implantation's internal storage of datetime values should be of
no relevance. In SQL Server land, datetime values are also stored as
double float but you don't see date1 - date2 type syntax down their
way.

Jamie.

--
 
Back
Top