How would I convert time values to words?

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Hello. I have a workbook I created to track break times at my work. I have a column of times in a workbook in the custom format [h]:mm:ss. Example 0:06:00 for six minutes. This column tells us how long the person went over the required break time. I would like to, in another column display this in words such as "6 minutes". I know using something like this (=MINUTE(N29)&" minutes") works. However, I would like to include the hours if there are any and if there are not any hours then not include them. For example, if a time is 1:24:45 I would like it to show 1 hour and 24 minutes. In the case of 0:06:00 I do not want it to say 0 hours and 6 minutes. How could I create a formula for excel to identify if there is an hour or not for this?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
How about:
varios 23jul2020b.xlsm
NO
2915:26:4515 Hour 26 Minutes
Hoja6
Cell Formulas
RangeFormula
O29O29=IF(HOUR(N29) > 0,HOUR(N29)& " Hour ","") & MINUTE(N29) & " Minutes"
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
How about:
varios 23jul2020b.xlsm
NO
2915:26:4515 Hour 26 Minutes
Hoja6
Cell Formulas
RangeFormula
O29O29=IF(HOUR(N29) > 0,HOUR(N29)& " Hour ","") & MINUTE(N29) & " Minutes"

Thank you that works for that. Upon doing this I thought of other scenarios. There might not be minutes just seconds. So is there a way to display the seconds also but only if there are any seconds? In essence I would like to display any time values in hours, minutes, and seconds and not display the time values with 0. I added an IF formula (attached) to display the seconds but doesn't check if there are any. Also I would need to check if there any minutes greater than 0. Thank you
seconds (break tracker).JPG
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
How about:

=IF(HOUR(A2) > 0,HOUR(A2)& " Hour ","") & IF(MINUTE(A2) > 0, MINUTE(A2) & " Minutes ","") & IF(SECOND(A2)>0,SECOND(A2)&" Seconds", "")
 
Solution

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
How about:

=IF(HOUR(A2) > 0,HOUR(A2)& " Hour ","") & IF(MINUTE(A2) > 0, MINUTE(A2) & " Minutes ","") & IF(SECOND(A2)>0,SECOND(A2)&" Seconds", "")

Thank you! All works perfectly. I really appreciate your help.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,904
Messages
5,544,978
Members
410,647
Latest member
LegenDSlayeR
Top