DateDiff in hours in excess of 24 - how to show as decimel value (1 Viewer)

Garmani

Registered User.
Local time
Today, 02:47
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
 

Minty

AWF VIP
Local time
Today, 09:47
Joined
Jul 26, 2013
Messages
10,355
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 ?
 

Garmani

Registered User.
Local time
Today, 02:47
Joined
Sep 23, 2019
Messages
18
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
can you post sample result.
 

Garmani

Registered User.
Local time
Today, 02:47
Joined
Sep 23, 2019
Messages
18
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

  • q_DHL_SL_Availability.xlsx
    202.7 KB · Views: 283

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
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])
 

Garmani

Registered User.
Local time
Today, 02:47
Joined
Sep 23, 2019
Messages
18
That's perfect - thank you so much for your help - much appreciated.
Best Regards
Garmani
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
you need to put the function in a Module.
 

Garmani

Registered User.
Local time
Today, 02:47
Joined
Sep 23, 2019
Messages
18
Yes - called the module from the query -

Thanks again
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
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

Top Bottom