Seemingly simple VBA macro is driving me crazy

youcancallmeal

This may seem pretty straightforward to some of you but its wrecking my head and I have spent most of yesterday and all today trying to figure it out.

Basically its an Excel VBA macro, there are cells containing numbers and I've calculated the average of them and output it to a message box. The numbers are actually minutes and what I want to do is have a second line in the message box that gives the average in a time format. i.e. it should something like "The is 45 minutes and 24 seconds"
Bit of a convoluted description but heres the code for the message box:

MsgBox "The Average time was " & Format(vAvg, "##.00") & " decimal minutes." _
& Chr(13) & "This is " & Format(vAvg, "mm:") & " minutes and " & format(vAvg, ":ss") " seconds"

Gosh

MsgBox "The Average time was " & Format(vAvg, "##.00") & " decimal minutes." _
& Chr(13) & "This is " & Format(vAvg, "mm:") & " minutes and " & format(vAvg, ":ss") " seconds"

you should be using "nn:" for the minutes Format and there is an & missing before " seconds"

youcancallmeal

Yep was just looking through the help and it is indeed "nn" also added the extra &
Hasn't made any difference though

The average as it stands on my sheet is 92.60(Decimal Minutes). So in theory the second line should read:

"This is 92 minutes and 36 seconds"

What it's reading is

"This is 24 minutes and 00 seconds"

I've no idea where this 24 is coming from never the seconds being 00??

Gosh

To use the "nn" and "ss" formats the variable must be a Date and Time - vAvg appears to be a number that's why the "nn" and "ss" doesn't format correctly.

Gosh

You could do the following on vAvg (assuming this is a decimal number)

Format(Int(vAvg), "##0")

to give you the minutes


and

Format((vAvg - Int(vAvg)) * 60, "00")

to give you the seconds

youcancallmeal

Thank you, worked a treat
Definitely wouldn't of thought of that, I was so caught up in trying to somehow convert the variable type to a date/time type halfway through the program??? It probably would of been impossible though because I had already declared vAvg as 'Double' at the start. Another option might have been to simply write the code to work out the average twice and assign vAvg to one of them and some variable defined as a date to the second one, probably wouldn't have worked though because I'm not aware of any way of splitting or hiding part of an answer to just display something like the seconds and not the minutes?
Well anyway thanks again.

cyberbob

a nicer way is as below
the date type is essentially a double containing number of days since new years eve 1899 , so once num mins is divided by num mins per day , you get a date
the date variable ( y ) is nicer to deal with for formatting and the like.

y =cdate( (vAvg / (24 * 60)) ) 
MsgBox Format(y, "nn:") & " minutes and " & Format(y, ":ss") & " seconds"