Returning a Difference Between Date/Time Values in HH.MM Format

[hhzyousafi]

Calling on all Access gurus!

I have been absolutely unable to figure this out and after 3 hours of "Googling" it seems what I am requesting is simply not out there - at least I have been unable to find anything similar.

I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:

Example 1:

Dim ActualManHours as Long

ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours

Example 2:

Dim ActualManHours as Long

ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime.value) Mod 60, "00")

Example 3:

Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long

StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours

 

[CazB]

When you say you want it in decimal format - do you mean you would want 30 mins to show as 0.50, and 45 mins as 0.75 ?

Because if that's the case, surely it's dead easy?

ActualManHours=format((txtEndTime-txtStartTime)*24,"standard")

 

[hhzyousafi]

Thank you CazB for the prompt response. Yes I would want 30 minutes to show as .50 and so on; however after trying the example you have given I am still not getting the desired result. For example I get the value "2.00" when I have a start time of 1:00 pm and an end time of 2:30 pm. The result, in this particular case, should be "1.50".

 

[jdraw]

You could try this

'---------------------------------------------------------------------------------------
' Procedure : Timediff
' Author    : Jack
' Date      : 11/12/2013
' Purpose   : To get the number of hours between a startdate and an end date.
'---------------------------------------------------------------------------------------
'
Sub Timediff()
      Dim StartTime As Double
      Dim EndTime As Double
      Dim ActualHours As Double
      Dim txtstarttime As String
      Dim txtendtime As String
10       On Error GoTo Timediff_Error

20    txtendtime = "10/12/13 2:30 PM"
30    txtstarttime = "8/12/13 1:00 PM"
40    StartTime = CDate(txtstarttime)
50    EndTime = CDate(txtendtime)
60    ActualHours = DateDiff("n", StartTime, EndTime) / 60 ' get minutes and divide by 60 for hours
      'txtActualHours = ActualHours
70    Debug.Print ActualHours

80       On Error GoTo 0
90       Exit Sub

Timediff_Error:

100       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Timediff of Module AWF_Related"
End Sub

 

[CazB]

ok there must be something different in your settings, as mine shows decimals.... eg 1.50, 4.00, 6.25... then again, I was just doing the calculation in a query rather than in SQL...

 

[hhzyousafi]

Thank you jdraw for the detailed response. I don't quite understand the function that you made. I dynamically pass values into the "Start Time" and "End Time" whereas in the function you have coded those times in? Perhaps I am simply misunderstanding it? CazB would you be kind enough to share the settings you have? The field I am entering this data into is a bound field that has a data type of "Number". I am not using SQL; however I am using VBA on the actual form. Does that make a difference?

 

[jdraw]

Here is a function and a test routine. You could call the function from a query.

Function:

'---------------------------------------------------------------------------------------
' Procedure : TimediffInHours
' Author    : Jack
' Date      : 11/12/2013
' Purpose   : Function to calculate and return the number of Hourse between 2 dates
'The dates are input as strings with a format similar to "21/3/13 4:58 PM"
'---------------------------------------------------------------------------------------
'
Function TimediffInHours(txtStarttime As String, txtEndtime As String) As Double
      Dim StartTime As Double
      Dim EndTime As Double
            
10       On Error GoTo TimediffInHours_Error
        
20    StartTime = CDate(txtStarttime)
30    EndTime = CDate(txtEndtime)
40    TimediffInHours = DateDiff("n", StartTime, EndTime) / 60 ' get minutes and divide by 60 for hours
50       On Error GoTo 0
60       Exit Function

TimediffInHours_Error:

70        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TimediffInHours of Module AWF_Related"

End Function

Routine to call/test the function

'---------------------------------------------------------------------------------------
' Procedure : testTimediff
' Author    : Jack
' Date      : 11/12/2013
' Purpose   : Test routine to execute the TimediffInHours function.
'---------------------------------------------------------------------------------------
'
Sub testTimediff()
      Dim txtMyEndTime As String
      Dim txtMyStartTime As String
10       On Error GoTo testTimediff_Error

20    txtMyEndTime = "10/12/13 2:30 PM"
30    txtMyStartTime = "8/12/13 1:00 PM"

40    Debug.Print TimediffInHours(txtMyStartTime, txtMyEndTime)

50       On Error GoTo 0
60       Exit Sub

testTimediff_Error:

70        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testTimediff of Module AWF_Related"
End Sub

 

[hhzyousafi]

I finally figured this out. Or I should say it was something so trivial that it didn't really require any figuring thus the issues with finding it on Google. The table the data was being stored in had the "StartTime" and "EndTime" declared as "Long" instead of "Double".