Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

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.

Comments

That’s all for today:) Subscribe to my email list and let’s have a discussion in the comments section!
Share this article on social media and follow me on Google+ and Twitter for more great VBA content!

Check out more VBA Tutorials

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.

Excel VBA Store