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
Write better macros in half the time
Join thousands of others improving their VBA knowledge for free with our email series. Subscribe for more of our best VBA tips and access to our entire macro library.
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
Result: 61 Hours 12 Minutes
Convert Minutes to Words
Result: 1 Hour 1 Minute 12 Seconds
Convert Seconds to Words
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!
Share this article on social media and follow me on Twitter and check out our VBA Q&A community for more great VBA content.