accumalitve hours and min between two dates n time (1 Viewer)

[k0r54]

Hi,

Is there a way i can work out the number of hours and min between two dates n time.

The dates n time will look like this: -

9/4/2006 17:47:00 PM - 11/4/2006 14:47:53

I just want it to tell me the number of hours and min between them.

Thanks
k0r54

 

[namliam]

You can use datediff (find it in the help for more)

Or you can do something
Hours: ((date1-date2) * 24)
Minutes: ((date1-date2) * 24 * 60)

Offcourse hours will return 1,5 for 1:30 and Minutes will return 90 ....

 

[k0r54]

Hi,

Hi the problem i seem to have though is it only goes up to 24 hours then starts again so anything where the dates are over 24 hours it just starts at 0 again ?

Thanks
k0r54

 

[namliam]

Thats why I showed you how to reset the dates to "real" numbers.

Hours: ((date1-date2) * 24)

Will go into 48 hours if needed, offcourse again 48.5 for 2 days and half an hour...

 

[k0r54]

Oh ok, sorry.

I just realised what you meant. Can that 48.5 be converted into 48:30 ?

Thanks
k0r54

 

[namliam]

Yeah sure, just get creative with the minutes and hours things that I allready gave you...

(or do you want me to spell it out for you? )

 

[k0r54]

lol im looking but not seeing.

it would be helpful to spell it ::confussed::

 

[k0r54]

ok, i have been sitting here racking my brains lol

no such luck - what am i missing

 

[namliam]

Ok I will help you along...

Hours: Int(((date1-date2) * 24))
Minutes: Int(((date1-date2) * 24 * 60))


This will return whole hours and minutes. Thus if you have
date1: 15-feb-2006 13:15
date2: 17-feb-2006 15:30

You have 50.25 hours, with above calculations:
Hours: 50
Minutes: 3015

You allready have your hours, now how would you get the 15 minutes?

 

[k0r54]

AHHHHHH

SOOO SIMPLE lol, havin a bad day of course.

60 * the number of hours (50) = 3000
3015 - 3000

50:15

Correct?

 

[namliam]

Kindoff looks that way... You must be regular Einstein

Now its one thing to do it in Pseudo code like this, now get it in a formula inside the query

 

[k0r54]

Working code: -

=IIf(Int(([localtime]-[reported])*24)<1,Format(([localtime]-[reported]),"h:nn"),Int(([localtime]-[reported])*24) & ":" & Format(Int((([localtime]-[reported])*24)*60)-(Int(([localtime]-[reported])*24)*60),"00"))

 

[k0r54]

That is used in a form text box.

 

[namliam]

I personaly wouldnt have done it this way, but heck...

And why not put it in a VBA function? Now you have something you can use on that form, but you need to copy paste it to use it in a report or something...

Using a (vba) function would allow you to keep the coding in one place....