Convert decimal number to minutes and seconds using VBA

vba time decimal seconds
user1371038 picture user1371038 · May 2, 2012 · Viewed 13.1k times · Source

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.

Answer

GSerg picture GSerg · May 2, 2012 
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.

Related questions

Display milliseconds in Excel

I am trying to display milliseconds in an Excel macro. I have a column of integers which are timestamps in milliseconds (e.g. 28095200 is 7:48:15.200 am), and I want to make a new column next to it which keeps a running …

Read More
How to get seconds since epoch (1/1/1970) in VBA?

How can I get seconds since epoch (1/1/1970) in VBA?

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More