In an attendance sheet or for legal purposes, you might want to display a time duration in words. For example, 10:25 should be converted to “Ten Hours and Twenty-Five Minutes.” You can convert a time duration into words using a formula in Google Sheets.
There is no built-in function in Google Sheets to convert a time duration into words. You might think of using the TEXT function, but it can only return the duration in text format, not in words.
What Is Time Duration?
When you have a start time and an end time, and you subtract the start time from the end time and format the result as a duration, you get the time duration.
You may also get larger durations like 182:00 when you sum multiple durations.
This usually appears in employee attendance, payroll, rent calculations based on duration, and similar use cases.
In such sheets, you might want to convert time durations into words.
Formula to Convert Time Duration into Words in Google Sheets
Assume you have the work start time in B2 and the end time in C2, and the duration after deducting a 1-hour lunch break (using the formula =C2-B2-(1/24) and formatted as Duration) in D2.
There are more start times, end times, and durations in the rows below, and the total duration is in cell D9, which is 37:30:00.
You can convert all time durations or just this total duration into words. Here, we are converting only the total duration.
Below is the formula used in cell D10:
=LET(
x, D9,
hr, INT(x*24),
hrl, IF(hr=0,"",IF(hr=1," Hour "," Hours ")),
m, MOD(INT(x*1440),60),
ml, IF(m=0,"",IF(m=1," Minute "," Minutes ")),
cn, IF(AND(hr>0,m>0)," and ",""),
word_1_19, LAMBDA(n, CHOOSEROWS(VSTACK(
"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten",
"Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen",
"Seventeen","Eighteen","Nineteen"), n)),
word_tens, LAMBDA(n, CHOOSEROWS(VSTACK(
"","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"), n)),
word_1_99, LAMBDA(n, IF(n<20, word_1_19(n),
TEXTJOIN("-", TRUE, word_tens(INT(n/10)), IFERROR(word_1_19(MOD(n,10)))))),
word_1_999, LAMBDA(n, LET(h, INT(n/100), r, MOD(n,100),
TEXTJOIN(" ", TRUE,
IF(h=0, "", word_1_19(h)&" Hundred"),
IF(r=0, "", word_1_99(r))))),
number_words, LAMBDA(n, LET(t, INT(n/1000), r, MOD(n,1000),
TEXTJOIN(" ", TRUE,
IF(t=0, "", word_1_19(t)&" Thousand"),
IF(r=0, "", word_1_999(r))))),
IF(hr>=20000, "Out of Range",
TRIM(number_words(hr)&hrl&cn&number_words(m)&ml)
)
)This formula returns “Thirty-Seven Hours and Thirty Minutes.”
When you use this formula for other ranges, replace D9 in the formula with the cell that contains the time duration you want to convert.
For example, if you want to convert all durations in the range D2:D8, replace D9 with D2 and enter the formula in cell E2. Then select cell E2 and drag the fill handle (the blue dot at the bottom-right corner of the cell) down.
This is how you can convert multiple time durations into words.
The formula converts time durations from 1 minute up to 19,999 hours and 59 minutes, and returns “Out of Range” for values outside this limit. This range should be sufficient for most practical use cases.
We can easily extend the range if needed, but I don’t think that will be necessary, so I’ve avoided unnecessary heavy lifting.
Formula Explanation
The formula uses LET and LAMBDA to create custom functions that convert numbers (hours and minutes extracted from the duration) into words.
There is one function for numbers 1 to 19, another for tens, then a function that uses these two to convert values in the range 1 to 99.
Another function builds on this to convert values from 1 to 999, and the final function converts values from 1 to 19,999.
x is the assigned name for the time duration.
x, D9hr is the hour part of the duration, and hrl returns Hour or Hours based on the hour value.
hr, INT(x*24),
hrl, IF(hr=0,"",IF(hr=1," Hour "," Hours "))m is the minute part of the duration, and ml returns Minute or Minutes based on the value.
m, MOD(INT(x*1440),60),
ml, IF(m=0,"",IF(m=1," Minute "," Minutes "))If both hr and m are greater than 0, the conjunction ” and “ is assigned to cn.
cn, IF(AND(hr>0,m>0)," and ","")word_1_19 is a custom function that returns words for numbers from 1 to 19 by calling word_1_19(n).
word_1_19, LAMBDA(n, CHOOSEROWS(VSTACK(
"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten",
"Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen",
"Seventeen","Eighteen","Nineteen"), n))word_tens returns the word for the tens place.
word_tens, LAMBDA(n, CHOOSEROWS(VSTACK(
"","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"), n))word_1_99 is another custom function that uses the previous two functions to convert hours or minutes in the range 1 to 99 into words.
word_1_99, LAMBDA(n, IF(n<20, word_1_19(n),
TEXTJOIN("-", TRUE, word_tens(INT(n/10)), IFERROR(word_1_19(MOD(n,10))))))word_1_999 converts hours or minutes in the range 1 to 999 using the previous function.
word_1_999, LAMBDA(n, LET(h, INT(n/100), r, MOD(n,100),
TEXTJOIN(" ", TRUE,
IF(h=0, "", word_1_19(h)&" Hundred"),
IF(r=0, "", word_1_99(r)))))number_words converts the hours or minutes part of the duration into words. The supported range is 1 to 19,999.
number_words, LAMBDA(n, LET(t, INT(n/1000), r, MOD(n,1000),
TEXTJOIN(" ", TRUE,
IF(t=0, "", word_1_19(t)&" Thousand"),
IF(r=0, "", word_1_999(r)))))The final expression converts the duration into hours and minutes in words, adds the conjunction and correct singular/plural labels, and limits the supported duration from 00:01 to 19999:59.
IF(hr>=20000, "Out of Range",
TRIM(number_words(hr)&hrl&cn&number_words(m)&ml)
)That’s how the formula converts a time duration into hours and minutes in words in Google Sheets.
DURATION_TO_WORDS Named Function
My formula makes converting time durations into words in Google Sheets much simpler. If you don’t want to use such a lengthy formula directly in your sheet, you can create a custom Named Function in Google Sheets.
You can check out my tutorial for this here: How to Create Named Functions in Google Sheets.
If you prefer to directly use the named function, you can make a copy of the sample sheet below:
Next, open the sheet in which you want to use the DURATION_TO_WORDS named function to convert durations into words.
Go to Data → Named functions → Import function, and import the function from the copied sheet by following the on-screen instructions.
Once imported, you can use it like this:
=DURATION_TO_WORDS(D9)That’s it—your time durations will now be converted into words automatically.
FAQs
Can Google Sheets convert time duration into words?
No. There is no built-in function. You must use a custom formula or a named function.
Does this work for large durations like 182:00?
Yes. It supports durations up to 19,999 hours and 59 minutes.
Will it work with summed durations?
Yes, as long as the result is formatted as Duration.
Are seconds supported?
No. Only hours and minutes are converted.
Can I apply it to multiple rows?
Yes. You can drag the formula down or apply it using MAP.
What happens for invalid or out-of-range values?
The formula returns “Out of Range.”
Can I use the named function in other sheets?
Only in sheets where the function is created or imported.
Conclusion
In this tutorial, you got your hands on a powerful formula to convert time durations into words. This can be extremely useful when creating invoices, templates, attendance sheets, and legal documents.
The formula is efficient, and the named function is built on the same core logic.
The main advantage of using the named function is its simplicity—it makes the conversion easy and reusable. The only limitation is that it works only in the workbook where the function is imported, which is a reasonable trade-off for cleaner and more readable sheets.
