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

__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!

#### About 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.*