Returning Minutes and Seconds

A

Anthony

hey there!

I want a message box to appear at the end of my sub, and say something like
this:

"Done. 1 minute and 40 seconds."

I know how to do this:

Dim sTime as string ' Start Time
Dim eTime as string ' End Time

sTime = Time

My code
My code
My code
My code
My code

eTime = Time

Msgbox "Done. " & chr(13) & chr(13) & "Start Time: " & sTime & chr(13) &
chr(13) & "End Time: " & eTime


But How do i get the minutes and seconds? I tried subtracting sTime from
eTime but it gives a weird number.
 
M

Marshall Barton

Anthony said:
I want a message box to appear at the end of my sub, and say something like
this:

"Done. 1 minute and 40 seconds."

I know how to do this:

Dim sTime as string ' Start Time
Dim eTime as string ' End Time

sTime = Time

My code

eTime = Time

Msgbox "Done. " & chr(13) & chr(13) & "Start Time: " & sTime & chr(13) &
chr(13) & "End Time: " & eTime

But How do i get the minutes and seconds? I tried subtracting sTime from
eTime but it gives a weird number.


That weird number is the (elapsed) time as the fraction part
of a day. Subtracting two times, will even confusing across
midnight. To deal with the midnight issue you need to use
Now instead of Time.

Then use the DateDiff function to get the number of seconds
in the difference and then format the result to your desired
string.

Dim sTime as Date ' Start Time
Dim eTime as Date ' End Time
Dim Elapsed As Long
Dim nl As String
nl = chr(13) & chr(10)

sTime = Now

My code

eTime = Now

Elapsed = DateDiff("s", sTime, eTime)
Msgbox "Done. " & nl & _
"Start: " & Format(sTime, "h:nn") & nl & _
"End: " & Format(eTime, "h:nn") & nl & _
"Elapsed: " & Elapsed \ 60 & ":" & _
Format(Elapsed Mod 60, "00")
 
J

John Vinson

But How do i get the minutes and seconds? I tried subtracting sTime from
eTime but it gives a weird number.

Date/Time values are stored as days and fractions of days. Try:

DateDiff("s", stime, etime) \ 60 & ":" & Format(DateDiff("s", stime,
etime) MOD 60, "00")

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top