datediff function (1 Viewer)

sroot

Registered User.
Local time
Today, 06:08
Joined
Mar 18, 2013
Messages
53
Hello everyone!

I have a query that i am trying to use the datediff function to get the hours between one point of time and today at 6:00AM and also one that shows between the same point of time and tomorrow at 6:00AM. Having that 6AM is throwing it off and i can't seem to figure it out. Thanks for the help!
 

jleach

Registered User.
Local time
Today, 09:08
Joined
Jan 4, 2012
Messages
308
Hi - can you show us the query/expressions you were trying to use? Can't quite figure out with the info you gave.

Cheers,
 

sroot

Registered User.
Local time
Today, 06:08
Joined
Mar 18, 2013
Messages
53
This is what i can use to make it work, but i don't want to have the date in there because then i have to change it every day... i would like to make it as easy as possible for everyone. I know i can use Date() instead of the actual date but then i can't figure out how to add 6:00 AM after it...

Code:
DateDiff("h",[rcv_hdr]![rcv_dt],(#9/19/2018 6:00:00 AM#))

and the one for tomorrows info

Code:
DateDiff("h",[rcv_hdr]![rcv_dt],(#9/20/2018 6:00:00 AM#))
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:08
Joined
Feb 19, 2013
Messages
16,555
what have you tried so far and why didn't it work?
here is a link to the datediff function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datediff-function

be aware that dates are stored as decimal numbers, the long integer part represent the date counting up from 31/12/1899 as 1 and today is 43362. The time element is the fractional part and is the time expressed in seconds divided by the number of seconds in a day (86400) - so 6AM is 6*60*60/86400 or more simply 6/24 = 0.25.

When using the datediff for hours you will get full hours whether they are complete or not. e.g. datediff will return 1 for a difference between 05:00 and 06:00 and 05:59 and 06:00 - if your 'one point in time' is an exact hour, it won't be a problem.

because dates are stored as numbers you can use maths instead - so for example 12am the day before yesterday and 6am today, the calc would be

43362.25-43360.5=1.75

so the time in hours would be

1.75*86400 to get the number of seconds (151200)
/60 gives number of minutes (2520)
/60 again gives number of hours (42)

the difference between 6am today and 6am tomorrow is 24 hours so add 24 to the above

edit: see there are additional posts. the easy way to get 6am today is

date()+0.25
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:08
Joined
May 7, 2009
Messages
19,175
just a hunch:

((datediff("d",[rcv_hdr]![rcv_dt],#9/20/2018 6:00:00 AM#)*24)+hour([rcv_hdr]![rcv_dt])+(minute([rcv_hdr]![rcv_dt])/60))-6
 

Users who are viewing this thread

Top Bottom