# DateDiff wont help me here, need something else

i know of the datediff function but i've tried it and it wont help me here. My problem is a Time difference calucation where i need to calculate the difference between 2 textboxes a Start time and an End time, BUT heres the problem and where the DateDiff Fails........If i input 6:00pm as a start time for example and 3:00am for an end time it will return the number 15 in the different of hours, which would be right if counting backwards, but would be wrong in my case, i need to know the different from 6:00pm to 3:00am which is 9 hours, the DateDiff only counts the times backwards.

Does anyone have any sort of formulation or idea on how to achieve this?

• [b][red]This message was edited by TazzyLynn at 2006-6-3 20:32:39[/red][/b][hr]
Ok on the DateDiff Function have you tried switching the values in the function? what I mean is if your function looks like this:
DateDiff("h", startshift, endshift) then possibly switch your 2 variables and see if it gives you the correct time. You may also need to add the actual date into the start and end times....

next time post what code you have, it lets us try and fix what is wrong.

: i know of the datediff function but i've tried it and it wont help me here. My problem is a Time difference calucation where i need to calculate the difference between 2 textboxes a Start time and an End time, BUT heres the problem and where the DateDiff Fails........If i input 6:00pm as a start time for example and 3:00am for an end time it will return the number 15 in the different of hours, which would be right if counting backwards, but would be wrong in my case, i need to know the different from 6:00pm to 3:00am which is 9 hours, the DateDiff only counts the times backwards.
:
: Does anyone have any sort of formulation or idea on how to achieve this?
:

• switching the variables will not work...if start time is 6:00pm and end time is 3:00am, switching them around to start time 3:00am and end time 6:00pm is still going to give me 15.....The DateDiff function only finds the difference between the two hours in the same day, meanings that it will count backwards from 6:00pm to 3:00am even thought 6:00pm is that start time.

a=hour(starttime.text) + (minute(startime.text) / 60)
b=hour( endtime.text) + (minute(endtime.text) / 60)
txtDisplay.text = (b + 24) -a ' This only works for a 24 hours clock.
if txtdisplay.text > 24 then
txtdispaly.text= txtdisplay.text - 24
end if
^^^^^ works perfectly, but only for 24 hour clock.

: [b][red]This message was edited by TazzyLynn at 2006-6-3 20:32:39[/red][/b][hr]
: Ok on the DateDiff Function have you tried switching the values in the function? what I mean is if your function looks like this:
: DateDiff("h", startshift, endshift) then possibly switch your 2 variables and see if it gives you the correct time. You may also need to add the actual date into the start and end times....
:
: next time post what code you have, it lets us try and fix what is wrong.
:
: : i know of the datediff function but i've tried it and it wont help me here. My problem is a Time difference calucation where i need to calculate the difference between 2 textboxes a Start time and an End time, BUT heres the problem and where the DateDiff Fails........If i input 6:00pm as a start time for example and 3:00am for an end time it will return the number 15 in the different of hours, which would be right if counting backwards, but would be wrong in my case, i need to know the different from 6:00pm to 3:00am which is 9 hours, the DateDiff only counts the times backwards.
: :
: : Does anyone have any sort of formulation or idea on how to achieve this?
: :
:
:
:
:

• : switching the variables will not work...if start time is 6:00pm and end time is 3:00am, switching them around to start time 3:00am and end time 6:00pm is still going to give me 15.....The DateDiff function only finds the difference between the two hours in the same day, meanings that it will count backwards from 6:00pm to 3:00am even thought 6:00pm is that start time.
:
: a=hour(starttime.text) + (minute(startime.text) / 60)
: b=hour( endtime.text) + (minute(endtime.text) / 60)
: txtDisplay.text = (b + 24) -a ' This only works for a 24 hours clock.
: if txtdisplay.text > 24 then
: txtdispaly.text= txtdisplay.text - 24
: end if
: ^^^^^ works perfectly, but only for 24 hour clock.
:
:
:
:
:
: : [b][red]This message was edited by TazzyLynn at 2006-6-3 20:32:39[/red][/b][hr]
: : Ok on the DateDiff Function have you tried switching the values in the function? what I mean is if your function looks like this:
: : DateDiff("h", startshift, endshift) then possibly switch your 2 variables and see if it gives you the correct time. You may also need to add the actual date into the start and end times....
: :
: : next time post what code you have, it lets us try and fix what is wrong.
: :
: : : i know of the datediff function but i've tried it and it wont help me here. My problem is a Time difference calucation where i need to calculate the difference between 2 textboxes a Start time and an End time, BUT heres the problem and where the DateDiff Fails........If i input 6:00pm as a start time for example and 3:00am for an end time it will return the number 15 in the different of hours, which would be right if counting backwards, but would be wrong in my case, i need to know the different from 6:00pm to 3:00am which is 9 hours, the DateDiff only counts the times backwards.
: : :
: : : Does anyone have any sort of formulation or idea on how to achieve this?

Seems to me the answer is adding a date component to each time, so DateDiff knows you're talking about 6:00 PM Today and 3:00 AM Tomorrow.

[size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

[code]
\$ select * from users where clue > 0
no rows returned
[/code]

• :
: Seems to me the answer is adding a date component to each time, so DateDiff knows you're talking about 6:00 PM Today and 3:00 AM Tomorrow.
:
I think date/time settings on his machine was not done correctly. On my machine [b]DateDiff("h", "6:00 PM", "3:00 AM") =[red]-15[/red][/b] and [b]DateDiff("h", "6:00 AM", "3:00 PM") = [red]9[/red][/b] with out date info.
• i got it to work....kinda...If the datediff returns a negative number, i just add that number to 24 which gives me the difference.
DateDiff("h","6:00pm","3:00am") = -15 + 24 = 9

: :
: : Seems to me the answer is adding a date component to each time, so DateDiff knows you're talking about 6:00 PM Today and 3:00 AM Tomorrow.
: :
: I think date/time settings on his machine was not done correctly. On my machine [b]DateDiff("h", "6:00 PM", "3:00 AM") =[red]-15[/red][/b] and [b]DateDiff("h", "6:00 AM", "3:00 PM") = [red]9[/red][/b] with out date info.
:

• Infidel is right ... without dates configured into the formula, the calculation of times is ambiguous ... what i mean is, how does the program know that we want the difference between 3am this morning and 6pm today (which is 15) or 6pm today and 3am tomorrow (which is 9.)

Below is the basis of the logic you are prob. looking for. You somehow have to attach tomorrows date to the time that is in the am. The code doesn't account for when both times are in the pm and both times are in the am.

[code]
Private Sub Command1_Click()
Dim d1 As Date
Dim d2 As Date

d1 = CDate(Date & " " & Text1.Text)
d2 = CDate(DateAdd("d", 1, Date) & " " & Text2.Text)

MsgBox Abs(DateDiff("h", d1, d2))
End Sub

Text1.Text = "6:00pm"
Text2.Text = "3:00am"
End Sub
[/code]
• Yeah this is the same concept as adding a day to the time ... in fact you may have logic like this ...

[code]
Option Explicit

Private Sub Command1_Click()
Dim d1 As Date
Dim d2 As Date
Dim diff As Integer

d1 = CDate(Text1.Text)
d2 = CDate(Text2.Text)

diff = DateDiff("h", d1, d2)

If diff > 0 Then
diff = (diff - 24) * -1
Else
diff = (diff + 24)
End If

MsgBox diff

End Sub

Text1.Text = "6:00pm"
Text2.Text = "3:00am"
End Sub
[/code]

However, the problem occurs when the user enters 2 am or 2 pm times.

: i got it to work....kinda...If the datediff returns a negative number, i just add that number to 24 which gives me the difference.
: DateDiff("h","6:00pm","3:00am") = -15 + 24 = 9
:
:
:
: : :
: : : Seems to me the answer is adding a date component to each time, so DateDiff knows you're talking about 6:00 PM Today and 3:00 AM Tomorrow.
: : :
: : I think date/time settings on his machine was not done correctly. On my machine [b]DateDiff("h", "6:00 PM", "3:00 AM") =[red]-15[/red][/b] and [b]DateDiff("h", "6:00 AM", "3:00 PM") = [red]9[/red][/b] with out date info.
: :
:
:

• So you prob will need something like this to handle all cases ...

[code]
Private Sub Command1_Click()
Dim d1 As Date
Dim d2 As Date
Dim diff As Integer

d1 = CDate(Text1.Text)
d2 = CDate(Text2.Text)

diff = DateDiff("h", d1, d2)

If IsSameTimeFrame(d1, d2) Then
diff = Abs(diff)
Else
If diff > 0 Then
diff = (diff - 24) * -1
Else
diff = (diff + 24)
End If
End If

MsgBox diff
End Sub

Text1.Text = "6:00pm"
Text2.Text = "3:00am"
End Sub

Function IsSameTimeFrame(ByVal d1 As Date, ByVal d2 As Date) As Boolean
IsSameTimeFrame = Right(d1, 2) = Right(d2, 2)
End Function
[/code]