Start Free Trial

asked on

Subtracting Times

i would like to work out the time difference between two times. Eg: if someone enters a building at 08:20:35am and leaves at 10:15:42am, i would like to work out exactly how long he has been there, well, preferably, the hours and minutes he has been there..
Can anyone help please?
Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Private Sub TimeDiff(StartTime, EndTime)
diff = Abs(DateDiff("s", StartTime, EndTime))
hrs = Int(diff / 3600)
diff = diff - hrs * 3600
mins = Int(diff / 60)
diff = diff - mins * 60
secs = diff
TimeDiff = Format(hrs, "00:") & Format(mins, "00:") & Format(secs, "00")
End Sub

This function returns the difference between two times using the datadiff function mcrider mentioned.

ASKER

TimCotee

Do you have to declare the:
diff, hrs, mins, secs, "s"

TimCotee...

Mixing sub and function...

That Code should actually be:

Function TimeDiff(StartTime, EndTime)
diff = Abs(DateDiff("s", StartTime, EndTime))
hrs = Int(diff / 3600)
diff = diff - hrs * 3600
mins = Int(diff / 60)
diff = diff - mins * 60
secs = diff
TimeDiff = Format(hrs, "00:") & Format(mins, "00:") & Format(secs, "00")
End Function

and you don't have to define anything, just drop it into a module

Cheers!

It would be good practice to, they are all integers except for the "s" which is a parameter to the datediff function and therefore doesn't need to be declared. Looking at this now I notice that I have put the starttime and endtime the wrong way round in the datediff function but the ABS around it will sort that out anyway.

Thanks mcrider for pointing out my obvious mistake, comes from bashing the keyboard without thinking too much.

By the way, you would use it this way:

ElapsedTime=TimeDiff("10/29/99 7:34:02","10/29/99 17:02:34")

Cheers!

TimCottee,

The line:

diff = Abs(DateDiff("s", StartTime, EndTime))

returns a long type not an integer type...

Cheers!

Again, you pick me up (correctly I might add) I didn't check before I replied!

ASKER

mcrider

Does the TimeDiff have to be declared somewhere?
It says sub or function is not defined?

No, it is a native VB function, just use it....

Cheers!

As mcrider pointed out, the code I gave you should read

Private Function TimeDiff(......
.....

End Function

This should be pasted into a module or a form. If it is in a module you may need to make it Public rather than private!

ASKER

mcrider

Does the TimeDiff have to be declared somewhere?
It says sub or function is not defined?

ASKER

mcrider

Does the TimeDiff have to be declared somewhere?
It says sub or function is not defined?

mscala, you appear to have posted the same comment three times now, have you seen my last comment to this?

ASKER

mcrider

Does the TimeDiff have to be declared somewhere?
It says sub or function is not defined?

Ok, do it like this:

1) create a module.

2) paste the following code into the Declarations Section of the module:

Public Function TimeDiff(StartTime, EndTime)
diff = Abs(DateDiff("s", StartTime, EndTime))
hrs = Int(diff / 3600)
diff = diff - hrs * 3600
mins = Int(diff / 60)
diff = diff - mins * 60
secs = diff
TimeDiff = Format(hrs, "00:") & Format(mins, "00:") & Format(secs, "00")
End Function

3) Then you can call TimeDiff anywhere in your program.

Cheers!

ASKER

mcrider

Does the TimeDiff have to be declared somewhere?
It says sub or function is not defined?

mscala,

Are you stuck in a loop? ;-)

ASKER

mcrider

Does the TimeDiff have to be declared somewhere?
It says sub or function is not defined?

ASKER

mcrider

yeah i am?

Are you old enough to remember LP Records.... and the trick of putting a penny on the stylus to keep it from skipping and repeating the same set of grooves? (BIG SMILE!)

ASKER

mcrider

yeah i am?

ASKER

Nah i don't know that stuff

in that code now,
what must declare diff, hrs, mins, secs as?

consider yourself pennied (BIGGEST SMILE I GOT!)

Did you get the step-by-step I gave you to work?

ASKER

Nah i don't know that stuff

in that code now,
what must declare diff, hrs, mins, secs as?

diff is a long, the others are integer

diff is a long, the others are integer

diff is a long, the others are integer

diff is a long, the others are integer

diff is a long, the others are integer

ASKER

The declarations?

Wants diff, and hrs etc, difined?

ASKER

The declarations?

Wants diff, and hrs etc, difined?

Public Function TimeDiff(StartTime, EndTime)

Dim diff as long
dim hrs as integer
dim mins as integer
dim secs as integer

diff = Abs(DateDiff("s", StartTime, EndTime))
hrs = Int(diff / 3600)
diff = diff - hrs * 3600
mins = Int(diff / 60)
diff = diff - mins * 60
secs = diff
TimeDiff = Format(hrs, "00:") & Format(mins, "00:") & Format(secs, "00")
End Function

Putting my penny on again!

Dim diff as Long
Dim hrs as Integer
Dim mins as Integer
Dim secs as Integer

Though I think the needle is going to break soon!

ASKER

The declarations?

Wants diff, and hrs etc, difined?

ASKER

The declarations?

Wants diff, and hrs etc, difined?

mscala

Wants computer seeing to!

ASKER

yes
it works
good stuff thanks a lot guys

At last an end! Glad to be of help to you.

Tim, I've posted a 50 point question for you... All you have you do is answer it and they're yours!

Cheers!

ASKER

yes
it works
good stuff thanks a lot guys