DateDiff in hours in excess of 24 - how to show as decimel value

Garmani

Registered User.
Local time
Today, 06:16
Joined
Sep 23, 2019
Messages
18
I have a DateDiff formula for elapsed time between two date/time fields extending over24 hours.
Elapsed Time: Abs(DateDiff([[Start],[Finish])\60) & Format(Abs(DateDiff("n",[Start],[Finish]) Mod 60),"\:00")

This returns a Short Time( hh:mm)

I have resolved the negative value problem using the Abs function, HOWEVER
I need to Group the resultant values into ranges - for example

> 4
> 8
>12
>24
>48

I seem to have no issues until I try to report > 24 - probably because I need to group on a decimal not a time value.

How can I convert the short time to a decimal - I have tried and failed consistently to find a solution ???

Thanks for any help with this mateer

Kind regards
Garmani
 
As you have discovered you can't have a time of >24 hours.
I'm not sure exactly what you are trying to actually get to?
If you had a minute value of 1830 would you expect to see 30:30 ?
 
Hi Minty - thanks for the response

In summary I am trying to find a method of saving the value returned by the DateDiff calculation in a fomat I can perform calaculations on.

Thus a result of say - 136:30 (HH:MM) would be 135.50 as a decimal which then allows to perform calculations in terms of grouping and sorting

Many thanks
Garmani
 
can you post sample result.
 
Hi Arnelgp
Attached excel extract of sample data output

Highlighted colums are the Date/Time fields and the SLA field is the output from the below

SLA: Abs(DateDiff("n",[VT11_DHL_Ver2]![Load_Complete],[ExportData]![asn_hdr__first_rcpt_date_time])\60) & Format(Abs(DateDiff("n",[VT11_DHL_Ver2]![Load_Complete],[ExportData]![asn_hdr__first_rcpt_date_time]) Mod 60),"\:00")


Regards
Garmani
 

Attachments

use this function to compute the hours (w/decimal):
Code:
Public Function HourDiff(dteSmall As Date, dteBig As Date) As Double
    Const OneMinute As Double = 0.0006944444
    Dim tmpDate As Date
    If dteSmall > dteBig Then
        tmpDate = dteSmall
        dteSmall = dteBig
        dteBig = tmpDate
    End If
    HourDiff = Round((CDec(dteBig) - CDec(dteSmall)) / 60 / OneMinute, 2)
End Function

on your query:

SLA: HourDiff([VT11_DHL_Ver2].[Load_Complete], [ExportData].[asn_hdr__first_rcpt_date_time])
 
That's perfect - thank you so much for your help - much appreciated.
Best Regards
Garmani
 
you need to put the function in a Module.
 
Yes - called the module from the query -

Thanks again
 
I say you have blank date there so I change the function:
Code:
Public Function HourDiff(dteSmall As Variant, dteBig As Variant) As Double
    Const OneMinute As Double = 0.0006944444
    Dim tmpDate As Date
    If IsEmpty(dteSmall) Or IsEmpty(dteBig) Then Exit Function
    If dteSmall > dteBig Then
        tmpDate = dteSmall
        dteSmall = dteBig
        dteBig = tmpDate
    End If
    HourDiff = Round((CDec(dteBig) - CDec(dteSmall)) / 60 / OneMinute, 2)
End Function
 

Users who are viewing this thread

Back
Top Bottom