Calculating Times Correctly

[JohnT]

Next problem...I've got 4 fields labeled as follows

TRAVELSTART
TRAVELEND
TRAVELSTART
TRAVELEND

Now, I've got my calculation expression so it will somewhat correctly add my total travel times on my form. The problem is when I jump past midnight on my times it obviously adds the times incorrectly because it doesn't know that the time past midnight is a new day. Example is as follows

TRAVELSTART1 is 18:00 (1/13/04
TRAVELEND1 is 21:00 (1/13/04
TRAVELSTART2 is 21:00 (1/13/04
TRAVELEND2 is 00:30 or 30 minutes past midnight (1/14/04

When my calculation is made on my form it gives me a TOTALTRAVEL of 17:30 or 17 hours 30 minutes. Not exactly accurate. Is there any way around this? And is there any way to convert my TOTALTRAVEL to a decimal representation of the time (ie. 19:30 to 19.5, 19:15 to 19.25, etc.)? Thanks in advance.

 

[Van T. Dinh]

Since you didn't post your expression, we don't know
what's wrong.

However, from your description, it seems that your store
only the Time components and NOT the Date components. In
this case, your expression probably has problems in
handling subtractions that involves mid-night.

See The Access Web article for possible solution:

<http://www.mvps.org/access/datetime/date0008.htm>

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Next problem...I've got 4 fields labeled as follows:

TRAVELSTART1
TRAVELEND1
TRAVELSTART2
TRAVELEND2

Now, I've got my calculation expression so it will

somewhat correctly add my total travel times on my form.
The problem is when I jump past midnight on my times it
obviously adds the times incorrectly because it doesn't
know that the time past midnight is a new day. Example is
as follows:

TRAVELSTART1 is 18:00 (1/13/04)
TRAVELEND1 is 21:00 (1/13/04)
TRAVELSTART2 is 21:00 (1/13/04)
TRAVELEND2 is 00:30 or 30 minutes past midnight (1/14/04)

When my calculation is made on my form it gives me a

TOTALTRAVEL of 17:30 or 17 hours 30 minutes. Not exactly
accurate. Is there any way around this? And is there any
way to convert my TOTALTRAVEL to a decimal representation
of the time (ie. 19:30 to 19.5, 19:15 to 19.25, etc.)?
Thanks in advance.

 

[JohnT]

Sorry about not including my expression. Good news is I actually figured it out for the most part using the link you provided Van. Thanks. Now what I haven't figured out is how to convert these times to a decimal representation (ie. 12:15 to 12.25 hours). All my times are based on quarters of every hour. I only record 15, 30 and 45 minutes past the hour or the hour itself (ie. 1200, 1215, 1230, 1245, 1300, etc.) so I figure it should be relatively simple to convert to decimal but I'm just not sure how. Anyway my code for adding my times are as follows:

Format([TRAVEL START 1] - 1 - [TRAVEL END 1], "Short Time")
Where TRAVEL START 1 and TRAVEL END 1 are entered in a Short Time format
and
Format([TRAVEL START 2] - 1 - [TRAVEL END 2], "Short Time")
Where TRAVEL START 2 and TRAVEL END 2 are entered in a Short Time format

What I am trying to achieve is in the following example:

TRAVEL START 1 = 08:00
TRAVEL END 1 = 09:15
TRAVEL START 2 = 10:00
TRAVEL END 2 = 11:00

These times are added and results sent to a control called TOTAL TRAVEL as 2:15. What I would like is for the TOTAL TRAVEL to be converted from the 2:15 to 2.25. If there is any way to convert these to decimal I would greatly appreciate the help. Thanks.

 

[Van T. Dinh]

If your [Total Travel] is DateTime (which I am not sure as you used Format
function and Format function returns String) then you can simply multiply it
by 24.

For example:

? #2:15# * 24
2.25

? #3:45# * 24
3.75

--
HTH
Van T. Dinh
MVP (Access)

Sorry about not including my expression. Good news is I actually figured

it out for the most part using the link you provided Van. Thanks. Now what
I haven't figured out is how to convert these times to a decimal
representation (ie. 12:15 to 12.25 hours). All my times are based on
quarters of every hour. I only record 15, 30 and 45 minutes past the hour
or the hour itself (ie. 1200, 1215, 1230, 1245, 1300, etc.) so I figure it
should be relatively simple to convert to decimal but I'm just not sure how.
Anyway my code for adding my times are as follows:

Format([TRAVEL START 1] - 1 - [TRAVEL END 1], "Short Time")
Where TRAVEL START 1 and TRAVEL END 1 are entered in a Short Time format
and
Format([TRAVEL START 2] - 1 - [TRAVEL END 2], "Short Time")
Where TRAVEL START 2 and TRAVEL END 2 are entered in a Short Time format

What I am trying to achieve is in the following example:

TRAVEL START 1 = 08:00
TRAVEL END 1 = 09:15
TRAVEL START 2 = 10:00
TRAVEL END 2 = 11:00

These times are added and results sent to a control called TOTAL TRAVEL as

2:15. What I would like is for the TOTAL TRAVEL to be converted from the
2:15 to 2.25. If there is any way to convert these to decimal I would
greatly appreciate the help. Thanks.

 

[JohnT]

Well, you were right. I get a "TYPE MISMATCH" error when I try to enter the times. Okay, I guess I need to go back a step here first. As I am sure you know by now, I have 4 fields for user input and 1 that updates from those 4 fields:

TRAVELSTART1 = user input field (SHORT TIME FORMAT)
TRAVELEND1 = user input field (SHORT TIME FORMAT)
TRAVELSTART2 = user input field (SHORT TIME FORMAT)
TRAVELEND2 = user input field (SHORT TIME FORMAT)

TOTAL TRAVEL = total of above 4 inputs (SHORT TIME FORMAT)

Using the FORMAT function worked great in the TOTAL TRAVEL field for adding my travel times, since I sometimes go beyond midnight and the FORMAT function seemed to work wonderfully with my times after midnight, as far as giving me the correct TOTAL TRAVEL time. Without using the FORMAT function I'm not sure how I can maintain the correct calculations in my TOTAL TRAVEL if my times run beyond midnight. However, I can't figure out how I can maintain the midnight issue and still get the times converted to decimal, since the FORMAT function returns a string. I'm sure there has to be a way to convert that string back to a datetime format but I've tried several methods with no success. I guess to make things easier to understand is I need to be able to calculate in my TOTAL TRAVEL field past midnight and at the same time, convert the TOTAL TRAVEL field to a decimal format. I hope this of some help and not more confusion. Thanks for all the great solutions.

 

[John Vinson]

Well, you were right. I get a "TYPE MISMATCH" error when I try to enter the times. Okay, I guess I need to go back a step here first. As I am sure you know by now, I have 4 fields for user input and 1 that updates from those 4 fields:

TRAVELSTART1 = user input field (SHORT TIME FORMAT)
TRAVELEND1 = user input field (SHORT TIME FORMAT)
TRAVELSTART2 = user input field (SHORT TIME FORMAT)
TRAVELEND2 = user input field (SHORT TIME FORMAT)

TOTAL TRAVEL = total of above 4 inputs (SHORT TIME FORMAT)

Using the FORMAT function worked great in the TOTAL TRAVEL field for adding my travel times, since I sometimes go beyond midnight and the FORMAT function seemed to work wonderfully with my times after midnight, as far as giving me the correct TOTAL TRAVEL time. Without using the FORMAT function I'm not sure how I can maintain the correct calculations in my TOTAL TRAVEL if my times run beyond midnight. However, I can't figure out how I can maintain the midnight issue and still get the times converted to decimal, since the FORMAT function returns a string. I'm sure there has to be a way to convert that string back to a datetime format but I've tried several methods with no success. I guess to make things easier to understand is I need to be able to calculate in my TOTAL TRAVEL field past midnight and at the same time, convert the TOTAL TRAVEL field to a decimal format. I hope this of some help and not more confusion. Thanks for all the great solutions.

Stop. You're going about this in the wrong way.

An Access Date/Time value is NOT A TEXT STRING. The format is applied
when it's ready to display. A Date/Time is stored as a double float
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. For instance, a value of #06:00 PM# is actually
stored as 0.75; and this really is equivalent to #12/30/1899 18:00:00#
on that long-ago pre-computer day.

If you will have cases where the travel starts before midnight and
ends after midnight, you would really do best to store the date and
time in the same field. To get a total of fractional hours, you can
use the Access DateDiff function to calculate the time in minutes, and
then divide by 60:

(DateDiff("n", [TravelStart1], [TravelEnd1]) +
DateDiff([TravelStart2], [TravelEnd2])) / 60.

Finally, if your table contains all four of these fields, *it is not
properly normalized*. I don't know what your table represents in the
real world, but I have to suspect that a given "trip" (or whatever it
is) might sometimes consist of one or of three travel legs; you may
want to consider having a one to many relationship with a "legs" table
with only one start and end time. This would let you use DateDiff to
calculate the duration of each leg, and a Totals query to sum them for
the whole trip.