subtracting time

[Scott]

I have 2 fields called timeBegin and timeEnd and both are medium time. I
need to get the difference in hours of the 2 fields.

DateDiff("h",[timeBegin],[timeEnd])) works fine, as long as my end time
doesn't go over 11:59 pm (i.e. the next day)


If I want to get the duration of 5:00 pm and 2:00 am (next day), how can I
compensate for after midnight?

 

[Douglas J. Steele]

Realistically, Access doesn't support time very well, except in the context
of a timestamp (both date and time). That's because of how Access stores
data in the Date data type: it's an 8 byte floating point number, where the
integral part represents the date as the number of days relative to 30 Dec,
1899, and the decimal part represents the time as a fraction of a day.

When you don't have a date, Access assumes it's 0 (which, if you recall from
above, is 30 Dec, 1899).

If you absolutely can't include the date with your time, see whether the
approach outlined in http://www.mvps.org/access/datetime/date0004.htm at
"The Access Web" works for you.

 

[Graham Mandeno]

Hi Scott

When you say a field is "medium time", what you mean is that it is formatted
as (i.e. *displays* as) a medium time. What is actually *stored* in the
field is a full date/time.

If you have entered only a time into the field (e.g. 11:20pm), then it
assumes the date part is "day zero", which is 30 December 1899. Therefore,
if you compare that with, say, "5:30am" the last time, being on the same
day, will be earlier than the first.

If you want to store start and end times, you should really store meaningful
dates along with them and then you don't have to make any assumptions. If
[timeBegin] is #19-Jul-2004 11:00pm# and [timeEnd] is #20-Jul-2004 5:00am#,
then your DateDiff expression will correctly give 6 hours.

If for some reason you don't want to record date as well as time
information, then you can simply add one day (24 hours) to the negative
result. This makes the assumption that the period will never be longer that
24 hours, so you could not start at 11pm on 19 July and end at 1am on 21
July. If you ignore the dates this would give a result of 2, not 26.

Also, you should be aware that DateDiff("h"...) only performs arithmetic on
the hour numbers, so for example:
datediff("h", "8:59:59", "10:00:00")
gives 2, when the real difference is only one second more that one hour.

For this reason, you may prefer to perform your DateDiff in minutes, or even
seconds, and then round the result as appropriate. For example:
round(datediff("n", "8:59:59", "10:00")/60, 1)
gives the expected result of 1.

 

[Scott]

i'm now just trying to append the time to a date, but keep getting type
mismatch in function.

how can i take 5:00 am and append "1/1/2004"?

if i could add a date value to the time, then datediff could take care of
rest.

in sql, i use the convert function to do these things, but cstr doesn't like
my "1/1/2004" no matter what type i use.

any ideas?

Hi Scott

When you say a field is "medium time", what you mean is that it is formatted
as (i.e. *displays* as) a medium time. What is actually *stored* in the
field is a full date/time.

If you have entered only a time into the field (e.g. 11:20pm), then it
assumes the date part is "day zero", which is 30 December 1899. Therefore,
if you compare that with, say, "5:30am" the last time, being on the same
day, will be earlier than the first.

If you want to store start and end times, you should really store meaningful
dates along with them and then you don't have to make any assumptions. If
[timeBegin] is #19-Jul-2004 11:00pm# and [timeEnd] is #20-Jul-2004 5:00am#,
then your DateDiff expression will correctly give 6 hours.

If for some reason you don't want to record date as well as time
information, then you can simply add one day (24 hours) to the negative
result. This makes the assumption that the period will never be longer that
24 hours, so you could not start at 11pm on 19 July and end at 1am on 21
July. If you ignore the dates this would give a result of 2, not 26.

Also, you should be aware that DateDiff("h"...) only performs arithmetic on
the hour numbers, so for example:
datediff("h", "8:59:59", "10:00:00")
gives 2, when the real difference is only one second more that one hour.

For this reason, you may prefer to perform your DateDiff in minutes, or even
seconds, and then round the result as appropriate. For example:
round(datediff("n", "8:59:59", "10:00")/60, 1)
gives the expected result of 1.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 2 fields called timeBegin and timeEnd and both are medium time. I
need to get the difference in hours of the 2 fields.

DateDiff("h",[timeBegin],[timeEnd])) works fine, as long as my end time
doesn't go over 11:59 pm (i.e. the next day)


If I want to get the duration of 5:00 pm and 2:00 am (next day), how can I
compensate for after midnight?

 

[Rick Brandt]

i'm now just trying to append the time to a date, but keep getting type
mismatch in function.

how can i take 5:00 am and append "1/1/2004"?

#5:00 am# + #1/1/2004#

 

[Scott]

my function looks like below, but i still get type error.

Function ConvertMyTime(sEndDate)
Dim xBeginDate As Date
xEndDate = "#" & "1/1/2004" & "#" & CDate(sEndDate)
end function

 

[scott]

here's my final function that solved subtracting times. thanks for all the
help.

Function DurationTime(sBeginTime, sEndTime)
Dim xBeginTime As String, xEndTime As String, xDate As Date, yDate As
Date, x As String, y As String

'usage: DurationTime(#2:00:00 PM#, #4:30:00 AM#)

xBeginTime = CStr(sBeginTime)
xEndTime = CStr(sEndTime)

If InStr(xBeginTime, "P") > 0 Then
x = "1/1/2004"
y = "1/2/2004"
Else
x = "1/1/2004"
y = "1/1/2004"
End If

xDate = CDate(x & " " & xBeginTime)
yDate = CDate(y & " " & xEndTime)

DurationTime = DateDiff("n", xDate, yDate) / 60

End Function

Hi Scott

When you say a field is "medium time", what you mean is that it is formatted
as (i.e. *displays* as) a medium time. What is actually *stored* in the
field is a full date/time.

If you have entered only a time into the field (e.g. 11:20pm), then it
assumes the date part is "day zero", which is 30 December 1899. Therefore,
if you compare that with, say, "5:30am" the last time, being on the same
day, will be earlier than the first.

If you want to store start and end times, you should really store meaningful
dates along with them and then you don't have to make any assumptions. If
[timeBegin] is #19-Jul-2004 11:00pm# and [timeEnd] is #20-Jul-2004 5:00am#,
then your DateDiff expression will correctly give 6 hours.

If for some reason you don't want to record date as well as time
information, then you can simply add one day (24 hours) to the negative
result. This makes the assumption that the period will never be longer that
24 hours, so you could not start at 11pm on 19 July and end at 1am on 21
July. If you ignore the dates this would give a result of 2, not 26.

Also, you should be aware that DateDiff("h"...) only performs arithmetic on
the hour numbers, so for example:
datediff("h", "8:59:59", "10:00:00")
gives 2, when the real difference is only one second more that one hour.

For this reason, you may prefer to perform your DateDiff in minutes, or even
seconds, and then round the result as appropriate. For example:
round(datediff("n", "8:59:59", "10:00")/60, 1)
gives the expected result of 1.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 2 fields called timeBegin and timeEnd and both are medium time. I
need to get the difference in hours of the 2 fields.

DateDiff("h",[timeBegin],[timeEnd])) works fine, as long as my end time
doesn't go over 11:59 pm (i.e. the next day)


If I want to get the duration of 5:00 pm and 2:00 am (next day), how can I
compensate for after midnight?

 

[Douglas J. Steele]

Why not try what Rick suggested?

Function ConvertMyTime(sEndDate)
Dim xBeginDate As Date
xEndDate = #1/1/2004# & CDate(sEndDate)
end function

Of course, that won't to anyting useful, since you're not assign the
function a value!

Function ConvertMyTime(sEndDate) As Date
ConvertMyTime = #1/1/2004# & CDate(sEndDate)
end function

 

[Graham Mandeno]

Hi Scott

You can do plain arithmetic with dates and times to a certain extent, but it
helps you understand what is going on if you know how they are stored:

The whole date/time value is a double-precision floating point number.
Within this, the whole number part represents that date, and is the number
of days since (or before) 30-Dec-1899 (which I call Z-Day). A value of 1
represents 31 Dec 1899, -1 is 29-Dec-1899, and today (21-Jul-2004) is 38189.
You can see this by typing ?CLng(Date) in the Immediate window.
The fractional part of the date/time "number" is the time during the day,
and is represented as the fraction of a day after midnight. So, for
example, 12 noon is .5, and 6am is .25. This means that 6pm this evening
will be 38189.75. You can try typing ?CDate(38189.75) into the Immediate
window to prove this.
One further note: the time part is *always* positive, so while it seems
logical that -1.25 is one day and 6 hours before Z-Day (28-Dec-1899 6:00pm)
it is actually time 0.25 on date -1 (29-Dec-1899 6:00pm). This quirk causes
strange results for people who, say subtract 8:00am from 6:00am and expect
to get -2:00.

It is important to understand that a date/time always represents a *point*
in time, never a time *interval*. VB has no native representation for a
time interval. Although it makes logical sense that you can subtract one
point in time from another and get the interval between them as the result,
VB does not work this way. Instead, the result is a meaningless third point
in time (usually) more than 100 years ago.

To answer your question then, you can combine a date value and a time value
using straight addition *provided* the date value is positive.

DateValue("1/1/2004") + TimeValue("5:00 am")
gives 01/01/2004 05:00:00 am

I could just as easily have used CDate instead of DateValue and TimeValue,
but the latter functions ignore the time part and the date part of the
argument respectively.

To return to your initial problem of recording start and end times, I
suggest you give each of the time textboxes an AfterUpdate event like this:

Private Sub TimeBegin_AfterUpdate()
If DateValue(TimeBegin) = 0 Then
TimeBegin = TimeBegin+ Date
If TimeBegin > Now Then TimeBegin = TimeBegin- 1
End If
End Sub

In other words: If the user enters a time with no date, then assume that
the date is today, unless that would result in a time in the future, in
which case assume the date is yesterday. If a (non-zero) date is explicitly
specified, then it is left alone.

It's then a simple matter to use DateDiff to calculate the difference
interval in whatever unit and to whatever precision you require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

i'm now just trying to append the time to a date, but keep getting type
mismatch in function.

how can i take 5:00 am and append "1/1/2004"?

if i could add a date value to the time, then datediff could take care of
rest.

in sql, i use the convert function to do these things, but cstr doesn't like
my "1/1/2004" no matter what type i use.

any ideas?

Hi Scott

When you say a field is "medium time", what you mean is that it is formatted
as (i.e. *displays* as) a medium time. What is actually *stored* in the
field is a full date/time.

If you have entered only a time into the field (e.g. 11:20pm), then it
assumes the date part is "day zero", which is 30 December 1899. Therefore,
if you compare that with, say, "5:30am" the last time, being on the same
day, will be earlier than the first.

If you want to store start and end times, you should really store meaningful
dates along with them and then you don't have to make any assumptions. If
[timeBegin] is #19-Jul-2004 11:00pm# and [timeEnd] is #20-Jul-2004 5:00am#,
then your DateDiff expression will correctly give 6 hours.

If for some reason you don't want to record date as well as time
information, then you can simply add one day (24 hours) to the negative
result. This makes the assumption that the period will never be longer that
24 hours, so you could not start at 11pm on 19 July and end at 1am on 21
July. If you ignore the dates this would give a result of 2, not 26.

Also, you should be aware that DateDiff("h"...) only performs arithmetic on
the hour numbers, so for example:
datediff("h", "8:59:59", "10:00:00")
gives 2, when the real difference is only one second more that one hour.

For this reason, you may prefer to perform your DateDiff in minutes, or even
seconds, and then round the result as appropriate. For example:
round(datediff("n", "8:59:59", "10:00")/60, 1)
gives the expected result of 1.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 2 fields called timeBegin and timeEnd and both are medium time. I
need to get the difference in hours of the 2 fields.

DateDiff("h",[timeBegin],[timeEnd])) works fine, as long as my end time
doesn't go over 11:59 pm (i.e. the next day)


If I want to get the duration of 5:00 pm and 2:00 am (next day), how

can

I