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.

GSerg · Answer 1 · 2012-05-02T21:32:56.183

4

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.

edited May 02 '12 at 21:32

answered May 02 '12 at 21:24

GSerg

76,472
17
159
346

1

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' – user1371038 May 02 '12 at 22:06

score 1 · Accepted Answer · edited Nov 24 '15 at 13:33

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.

Convert decimal number to minutes and seconds using VBA

2 Answers2