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.

That’s all for today:) Subscribe to my email list and let’s have a discussion in the comments section!

#### Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too