Formatting timer display in MsgBox

[Student1990]

Hi all. I have a routine that I run daily, and when finished, it displays a message box announcing its completion and the time taken. However, when the MsgBox comes up, the time is in seconds, and usually out to 7+ decimal places. How can I format the timer to display either mm:ss or just seconds, but without any decimals?

My shortened code is below, leaving out the unrelated stuff in the middle. I thought the formatting I placed in there would do the trick, but it doesn't seem to. Can anyone assist? As always, help is much appreciated. Thanks for reading.

Please Login or Register  to view this content.

[joeu2004]

[.... deleted ....]

[joeu2004]

Sorry for the incessant posting. Too many corrections and too late to simply edit.

How can I format the timer to display either mm:ss or just seconds, but without any decimals?
[....]

Please Login or Register  to view this content.

First, Timer returns seconds since midnight. So "Duration" might be a misleading variable name as you have used it.

Second, the two uses of Duration are inconsistent. I suspect the first use should be Format(Timer - Duration,...), like the second use.

To format as "mm:ss", use:

Format((Timer - Duration)/86400, "mm:ss")

because "mm:ss" expects a value that is Excel time, which is a fraction of a day (86400 seconds).

To format as seconds, use:

WorksheetFunction.Round(Duration,0)

I prefer not to use VBA Round because it rounds differently than Excel ROUND.

PS.... The string Format(..., "[m]:ss") & " seconds." [sic] seems misleading. "12:34 seconds" is incorrect because your format is 12 minutes 34 seconds.

[Student1990]

Thank you - that solves the problem. I really didn't understand Timer and that its seconds since midnight - this is really the key concept here. Much appreciated, Joeu2004.

-Student1990