Geek asks and answers

Convert decimal to minutes and seconds using VBA

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

Example: A number to a decimal point gives minutes as 0.197683577 (0 minutes) and a number times 60 gives seconds = 0.197683577 * 60 = 11.86101462 (12 seconds) = 0:12

I know that I can do this by copying and pasting into a text format and dividing the number left and right, using the text in the decimal point columns, and then add a “:” between them, but there should be a way to calculate this.

+3
source share
2 answers

The VBA function did not work for me, but I found a workaround in Excel:

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

W11 - , 900.3201306. - 15:00

, + , . decimal seconds * 60, seconds+milliseconds. , . 0, "00", . .

.

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

:

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

, . TimeSerial (11.86 → 12), TIME (11.86 → 11). .

+4
source

More articles:


All Articles