Difference in time

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

Guest

Trying to subtract the end time - the start time in a text box, but it keeps
giving me the absolute value, even when start time is greater than end time.
Here is my code the the calculating text box with the format "h":

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

where End_Time and Start_Time are Text boxes on my form with the format of
"h AM/PM"
 
h Display the hour as a number without leading zeros (0 - 23).
Hh Display the hour as a number with leading zeros (00 - 23).
N Display the minute as a number without leading zeros (0 - 59).
Nn Display the minute as a number with leading zeros (00 - 59).
S Display the second as a number without leading zeros (0 - 59).
Ss Display the second as a number with leading zeros (00 - 59).
t t t t t Display a time as a complete time (including hour, minute,
and second), formatted using the time separator defined by the time format
recognized by your system. A leading zero is displayed if the leading zero
option is selected and the time is before 10:00 A.M. or P.M. The default
time format is h:mm:ss.
AM/PM Use the 12-hour clock and display an uppercase AM with any hour
before noon; display an uppercase PM with any hour between noon and 11:59
P.M.
 
Yes, i know all of this, that is why i was able to change it to "h". You did
not mention anything about subtracting time, which is the answer that i am
seeking.
 
Do End_Time and Start_Time include dates, or are you strictly storing time
in there?

The Date/Time data type in Access is intended to hold complete timestamps
(date and time).
 
Matt said:
Yes, i know all of this, that is why i was able to change it to "h".
You did not mention anything about subtracting time, which is the
answer that i am seeking.
Click to expand...

People can write the following on a piece of paper...

7:45

....and it can mean either the point on the clock (seven forty-five) or it can
mean an amount of time (seven hours and forty-five minutes). In Access
however, this ALWAYS means the point in time (seven forty-five) and never a
duration.

Abstractly, subtracting one DateTime from another is a meaningless excercise.
What is 8 O'Clock minus 4 O'Clock? What is Thursday divided by Friday? The
reason that this can be done in Access to some degree is because Access stores
dates under the covers as numbers based on a reference DateTime of Midnight on
12/30/1899. This is a convenience and solves many date manipulation problems,
but one must keep in mind that "date math" is only possible because of how
Access has chosen to store dates and that there are some limitations.

In Access 1:00 + 2:00 will yield 3:00. What that expression actually does
though is add the numeric representations of...

12/30/1899 01:00:00 + 12/30/1899 02:00:00

....and the result is the number that represents 12/30/1899 03:00:00. Great
right? However; if the math results in a different DAY and your expression only
displays or concerns itself with the TIME then this all starts to break down.

23:00:00 + 3:00:00 = 2:00:00

This is because the 2 am result is actually on 12/31/1899 instead of on
12/30/1899. In your case you are subtracting two DateTimes and the result is
going to be a DateTime. There is no such thing as a "negative" DateTime. If
you add two dates in this manner you get a date. If you subtract two dates you
get a date.

For this reason it is not generally recommended to do "date math" with simple
arithmetic expressions. It is better to use DateDiff() to determine how many
(days, hours, seconds, etc.), there are between the operands and then converting
that result back into an expression that looks like a duration like hh:nn.

Since DateDiff gives you an integer response indicating how many "intervals"
there are between the two dates then it will return a negative value if the
second date is earlier and a positive value if the second date is later.
Straight arithmetic on two dates will never do this.
 
Back
Top