Convert Decimal Time to Hours Minutes Seconds with VBA

Introduction - Convert Decimal Times to Words

This VBA function will convert numbers written in decimal format in Excel, like 10.5, to words, like 10 Minutes 30 Seconds. It’s flexible enough to allow you to change your base unit, so if you rather 10.5 be in hours, it will convert it to 10 Hours 30 Minutes.

It’s entirely up to you! Whatever you toss at it, this UDF will handle it.

Example - VBA UDF to Convert Decimal Times to Words

Function TimeToWords(rng1 As Variant, Optional units As String)
'DEVELOPER: Ryan Wells (wellsr.com)
'DATE: 08/2016
'DESCRIPTION: Converts decimal times to words.
'             Function is capable of converting hours, minutes and seconds from
'             decimal format (10.5) to words ("10 Minutes 30 Seconds").
'EXAMPLES: =TimeToWords(A1) to convert from minutes (default)
'          =TimeToWords(A1,"h") to convert from hours
'          =TimeToWords(A1,"s") to convert from seconds
'          =TimeToWords(7.5) converts to "7 Minutes 30 Seconds"
Dim dSec As Double, dMin As Double, dHrs As Double
Dim strHrs As String, strMin As String, strSec As String
Dim dBase As Double
If IsNumeric(rng1) = False Then GoTo 101:

'Check if base unit is hours, minutes or seconds. Default is minutes
units = UCase(units)
dBase = rng1
If units = "H" Or units = "HOUR" Or units = "HR" Or units = "HOURS" Or units = "HRS" Then
    dMin = Application.WorksheetFunction.RoundDown(dBase * 60, 0)
    dSec = dBase * 60 - dMin
ElseIf units = "S" Or units = "SECOND" Or units = "SEC" Or units = "SECONDS" Or units = "SECS" Then
    dMin = Application.WorksheetFunction.RoundDown(dBase / 60, 0)
    dSec = dBase / 60 - dMin
ElseIf units = "M" Or units = "MINUTE" Or units = "MIN" Or units = "MINUTES" Or units = "MINS" Or units = "" Then
    dMin = Application.WorksheetFunction.RoundDown(dBase, 0)
    dSec = dBase - dMin
Else
    TimeToWords = "Invalid units entered"
    Exit Function
End If

'calculate hours, minutes, and seconds based on base unit provided
dSec = Round(dSec * 60, 0)
If dMin >= 60 Then
    dHrs = dMin / 60
    dMin = (dHrs - Application.WorksheetFunction.RoundDown(dHrs, 0)) * 60
    dHrs = Application.WorksheetFunction.RoundDown(dHrs, 0)
    dMin = Round(dMin, 0)
End If
 
'handle plural vs not
If dHrs = 1 Then
    strHrs = dHrs & " Hour "
ElseIf dHrs = 0 Then
    strHrs = ""
Else
    strHrs = dHrs & " Hours "
End If
 
If dSec = 1 Then
    strSec = dSec & " Second "
ElseIf dSec = 0 Then
    strSec = ""
Else
    strSec = dSec & " Seconds "
End If
 
If dMin = 1 Then
    strMin = dMin & " Minute "
ElseIf dMin = 0 Then
    strMin = ""
Else
    strMin = dMin & " Minutes "
End If
 
'final results
TimeToWords = strHrs & strMin & strSec
Exit Function

'handle errors
101:
TimeToWords = "Non-numeric value entered as an argument"
End Function

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

Tutorial - Convert Decimal Times to Words

Copy the above macro into a module in Excel and whenever you need to convert a number to words, type something like =TimeToWords(A1) into a cell in Excel to convert the number in cell A1 to hours, minutes and seconds.

By default, this function assumes your numbers are in minutes. I defaulted to minutes because I enjoy running and I developed this to convert my minutes per mile rates to words.

Here are a few alternative formula entries if your base units are in hours, minutes are seconds. Let’s assume cell A1 contains the number 61.2.

Convert Hours to Words

=TimeToWords(A1, "h")
Result: 61 Hours 12 Minutes

Convert Minutes to Words

=TimeToWords(A1, "m")
Result: 1 Hour 1 Minute 12 Seconds

Convert Seconds to Words

=TimeToWords(A1, "s")
Result: 1 Minute 1 Second

I want you to notice two things about how this function behaves.

First, see how the function handles plural numbers? It’s smart enough to know whether it should say “Minutes” or “Minute” depending on your input!

Lastly, notice how the function rounds 0.2 seconds down to the nearest second in the third example. Unless you’re a short distance sprinter, that shouldn’t be a big deal.

Application Ideas - Convert Decimal Times to Words

Released just in time for the Olympics Rio 2016, this function is great for converting race times.

I use this to project my final times when running at a certain pace over a particular distance. Why convert in my head when I can get Excel to do it for me?

If you’re not a runner, there are many other ways you can use this. For example, you can convert your time sheet to hours and minutes or if you’re a freelancer, you can easily convert your invoices from decimal hours to hours and minutes.

When you subscribe to my email list, you’ll have access to a free Excel Add-In that contains this macro and many more of my user defined function creations.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.