Convert decimal number to minutes and seconds using VBA

Question

I have time represented by a decimal number that I need to convert to minutes and seconds in Excel using VBA.

Example: The number before the decimal point gives the minutes as 0.197683577 (0 minutes) and the number multiplied by 60 gives the seconds = 0.197683577*60 = 11.86101462 (12 seconds) = 0:12

I know I could do this by copying and pasting into text format and separating the number to the left and right using text to columns of the decimal point and then add ":" in between but there must be a way to calculate this.

Solution 1

The VBA function didn't work for me but I found a workaround within Excel:

=TRUNC(W11/60)&":"&IF(ROUND(((W11/60-TRUNC(W11/60))*60),0)=0,"00",ROUND(((W11/60-TRUNC(W11/60))*60),0))`

Where W11 is time in the form of a decimal number like 900.3201306. The result is in minutes 15:00

Basically, you take the truncated decimal minutes minus the decimal minutes+seconds to give you seconds only. Then you take the decimal seconds * 60 to give you seconds+milliseconds. Round this to give you seconds. If seconds are 0 then write "00" to get the correct format. Then add the truncated minutes plus the seconds.

This formula seems to be working.

Solution 2

Public Function FromDecimalTime(ByVal t As Double) As Date
  FromDecimalTime = TimeSerial(0, Fix(t), (t - Fix(t)) * 60)
End Function

Same with a formula:

=TIME(0,TRUNC(A1),(A1-TRUNC(A1))*60)

Well, not the same actually. TimeSerial will round number of seconds according to rounding rules (11.86 -> 12), and TIME will truncate (11.86 -> 11). But you can apply a rounding function to its last argument.

Comments

[user1371038]

Thanks a lot! Since I wanted an integer for the minutes I used Fix(decim) & Format(val, ":ss") to get the proper format. I'm not sure if there is a time format code to generate this but it seems to work. 'Sub aaaaaaaMacro1() Dim decim As Double decim = Range("AB11").Value Dim val As Date val = TimeSerial(0, Fix(decim), (decim - Fix(decim)) * 60) MsgBox Fix(decim) & Format(val, ":ss") End Sub'