Returning Minutes and Seconds

[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.

 

[Marshall Barton]

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")

 

[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]