471,066 Members | 1,191 Online

# 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
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
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
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.