Date and time Diff

Gismo

Registered User.
Local time
, 01:11
Joined
Jun 12, 2017
Messages
1,298
Hi all,
I have a date and time received :2019/01/21 02:05:00
and a date and time delivered: 2019/01/23 10:13:00
I need to know what is the difference in days, hours and minutes
Tried the datediff, days work out fine but hours is not correct
it can all be in one field or Days in one field and hours / min in a different field
my result is 2 Days, 8 hours and 0 min which is obviously not correct.
Any suggestions would be appreciated.
 
use datediff to get the number of minutes

then work out the number of days by using the \ and mod operators (there are 24*60 minutes in a day)

with the remainder, use the operators to determine the number of hours with the final remainder being the number of minutes

Code:
function getTime(Date1 as Date, Date2 as Date) as String
dim ttlMins as long
dim calcdays as long
dim remMins as long
dim calcHours as long
dim calcMins as long
 
    ttlMins=datediff("n",date1,date2)
    calcDays=ttlmins\(24*60)
    remMins=ttlmins mod (24*60)
    calcHours=remMins\60
    calcMins=remMins mod 60
 
    getTime=calcDays & " Days, " & calcHours & " Hours and " & calcMins & " Minutes
 
end function
 
use datediff to get the number of minutes

then work out the number of days by using the \ and mod operators (there are 24*60 minutes in a day)

with the remainder, use the operators to determine the number of hours with the final remainder being the number of minutes

Code:
function getTime(Date1 as Date, Date2 as Date) as String
dim ttlMins as long
dim calcdays as long
dim remMins as long
dim calcHours as long
dim calcMins as long
 
    ttlMins=datediff("n",date1,date2)
    calcDays=ttlmins\(24*60)
    remMins=ttlmins mod (24*60)
    calcHours=remMins\60
    calcMins=remMins mod 60
 
    getTime=calcDays & " Days, " & calcHours & " Hours and " & calcMins & " Minutes
 
end function
I am not getting the desired result. is my format for date and time correct to use the DateDiff function in a query - "2019/01/21 02:05:00"

These are actually the date field and the time field that I have joined.

I am not sure where to use the code that you have supplied.
 
Last edited:
without any info, not sure what to suggest

I am not getting the desired result
provide example data, the result you get and the required result. Note you cannot format date differences as a date

I am not sure where to use the code that you have supplied.
put it in a module and call it from wherever - presumably a query
 
Minor correction: Make that a PUBLIC function and put it in a general module (as opposed to a form's class module) if you want to be able to use it in queries.
 

Users who are viewing this thread

Back
Top Bottom