Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-23-2019, 12:27 AM   #1
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
DateDiff in hours in excess of 24 - how to show as decimel value

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

Garmani is offline   Reply With Quote
Old 09-23-2019, 01:03 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,737 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: DateDiff in hours in excess of 24 - how to show as decimel value

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 ?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-23-2019, 01:12 AM   #3
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Re: DateDiff in hours in excess of 24 - how to show as decimel value

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

Garmani is offline   Reply With Quote
Old 09-23-2019, 01:16 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DateDiff in hours in excess of 24 - how to show as decimel value

can you post sample result.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 01:40 AM   #5
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Re: DateDiff in hours in excess of 24 - how to show as decimel value

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
Attached Files
File Type: xlsx q_DHL_SL_Availability.xlsx (202.7 KB, 7 views)
Garmani is offline   Reply With Quote
Old 09-23-2019, 02:12 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DateDiff in hours in excess of 24 - how to show as decimel value

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])
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Garmani (09-23-2019)
Old 09-23-2019, 02:37 AM   #7
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Re: DateDiff in hours in excess of 24 - how to show as decimel value

That's perfect - thank you so much for your help - much appreciated.
Best Regards
Garmani

Garmani is offline   Reply With Quote
Old 09-23-2019, 02:44 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DateDiff in hours in excess of 24 - how to show as decimel value

you need to put the function in a Module.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 03:08 AM   #9
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Thumbs up Re: DateDiff in hours in excess of 24 - how to show as decimel value

Yes - called the module from the query -

Thanks again
Garmani is offline   Reply With Quote
Old 09-23-2019, 03:40 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DateDiff in hours in excess of 24 - how to show as decimel value

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 06:28 AM   #11
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Thumbs up Re: DateDiff in hours in excess of 24 - how to show as decimel value

Thanks works fine with the null values

Garmani is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Working hours using DateDiff between 2 different dates and times veraloopy Modules & VBA 16 10-24-2011 03:40 AM
DateDiff to show hours and mins echo0001 Queries 6 12-29-2010 09:02 PM
DateDiff Problem with Hours voslica Queries 2 08-29-2007 08:16 AM
Time in excess of 24 hours JordanR Forms 5 04-11-2006 06:48 AM
datediff minutes and hours Cereldine Queries 1 02-03-2006 04:15 AM




All times are GMT -8. The time now is 09:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World