471,066 Members | 1,191 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Subtracting time past midnight

I want to be able to subtract two time fields, one starting before
midnight and the other past midnight. example:
starttime 23:00 and endtime 01:10
The answer is 1:10.
How can I do this with all times on the 24 hour clock?
Nov 13 '05 #1
4 5891
On Mon, 29 Nov 2004 05:22:25 GMT, Robert Schoenert <rs********@mindspring.com>
wrote:
I want to be able to subtract two time fields, one starting before
midnight and the other past midnight. example:
starttime 23:00 and endtime 01:10
The answer is 1:10.
How can I do this with all times on the 24 hour clock?


Easy. If you always know what the time direction should be, then any negative
number result needs to have 1 day added to it.

Public Function TimeDiff( _
StartTime As Variant, _
EndTime As Variant _
) As Variant
varTimeDiff As Variant

varTimeDiff = EndTime - StartTime
If varTimeDiff < 1 Then
varTimeDiff = varTimeDiff + 1.0 ' 1.0 = 1 day
End If

' CVDate in case data type is changed during computation.
TimeDiff = CVDate(varTimeDiff)
End Function
Nov 13 '05 #2
Add a day to the EndTime if it is less than the StartTime:

Minutes: DateDiff("n", [StartTime], [EndTime] + IIf([EndTime] <
[StartTime]), 1, 0))

For instructions on formatting that as hours and minutes, see:
Calculating elapsed time
at:
http://members.iinet.net.au/~allenbrowne/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Schoenert" <rs********@mindspring.com> wrote in message
news:hv********************************@4ax.com...
I want to be able to subtract two time fields, one starting before
midnight and the other past midnight. example:
starttime 23:00 and endtime 01:10
The answer is 1:10.
How can I do this with all times on the 24 hour clock?

Nov 13 '05 #3
Steve Jorgensen wrote:
If varTimeDiff < 1 Then


I'd change that to a while loop, to catch the realy slow ones :-)
--
This sig left intentionally blank
Nov 13 '05 #4
On Mon, 29 Nov 2004 08:29:58 +0000, Trevor Best <no****@besty.org.uk> wrote:
Steve Jorgensen wrote:
If varTimeDiff < 1 Then


I'd change that to a while loop, to catch the realy slow ones :-)


If there's no date information, then the diff could never be greater than 1
day. If there is date information, then we could simply subtract, and
multiply by 24 to get a result in fractional hours, and the negative should
never come up.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Kevin Lin | last post: by
4 posts views Thread by richardkreidl | last post: by
17 posts views Thread by Franc Zabkar | last post: by
1 post views Thread by Drum2001 | last post: by
1 post views Thread by John | last post: by
4 posts views Thread by Chris Shenton | last post: by
25 posts views Thread by Brian | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.