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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How would I convert time values to words?
DanteAmor
Well-known Member
- Joined
- Dec 3, 2018
- Messages
- 17,833
- Office Version
- 2019
- Platform
- Windows
How about:
| varios 23jul2020b.xlsm | ||||
|---|---|---|---|---|
| N | O | |||
| 29 | 15:26:45 | 15 Hour 26 Minutes | ||
Hoja6 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O29 | O29 | =IF(HOUR(N29) > 0,HOUR(N29)& " Hour ","") & MINUTE(N29) & " Minutes" |
Upvote
0
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
Upvote
0
Thank you! All works perfectly. I really appreciate your help.
Upvote
0
Similar threads
- Solved
