Elapsed Time calculations

Posted by Adrian Fisher on February 13, 2002 4:59 PM

I am trying ot convert elapsed times (in mm:ss format) to total seconds. I can do it with a simple multiplication of the serial number as long as the minutes do not exceed 24, but beyond that the calculations go astray. I've tried using the TimeValue function as an intermediate step but that doesn't help either. How can I do this?

Posted by DRJ on February 13, 2002 5:04 PM

Do you want to calculate the elapsed time it takes for a macro to run? or total time the workbook is open?

For macro try this

Sub MyMacro()
MyTime = Now
Your Code Here
MyTime = Now - MyTime

End Sub

Then you can put this value whereever you want.

For total time open try this

In A1 or whereever put = now()

Format how you want.

Then in the workbook_open sub put

call MyTimer

Then make a macro in a regular module

Sub MyTimer()
calculate
application.ontime now + timeserial(0,0,1), "MyTimer"

End Sub

HTH

DRJ

Posted by Barrie Davidson on February 13, 2002 6:17 PM

Assuming your elapsed time is recognized as minutes and seconds in Excel, you can use this formula to return the seconds (format as General or whatever number format you desire):

=A1*86400

Regards,
Barrie
Barrie Davidson