Skip to main content

How to quickly convert time to text or number of hours/minutes/seconds in Excel?

Author: Sun Last Modified: 2020-06-29

In this tutorial, I introduce the methods to convert time to text string or number string, or the number of decimal hours, minutes and seconds in Excel.

Convert time to text string

Convert time to number string

Convert time to number of hours/minutes/seconds


Convert time to text string

Here is a formula can convert time to text string.

Select the cell which will place the text result, type this formula =TEXT(A1,"hh:mm:ss AM/PM"), press Enter key. And if you need, drag fill handle down to apply this formula to the below cells.

doc time to text number 1
doc time to text number 2

Convert time to number string

To convert time to number string, you just need to format the time cells as General.

Select the time cells you want to convert, click Home tab, go to Number group, select General from the drop down list.
doc time to text number 3


Convert time to number of hours/minutes/seconds

If you want to convert time to number of hours/minutes/seconds, you can choose one of below methods to deal with.

Convert time to number of hours/minutes/seconds with formula

Time Formula Result
16:15:48      =A1*24 16.26
16:15:48 =A1*1440 975.8
16:15:48 =A1*86400      58548      

Remember to format the result cells as general.

Convert time to number of hours/minutes/seconds with Kutools for Excel

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

Select the cells you want to convert, click Kutools > Content > Convert Time > Time to Hours/Time to Minutes/Time to Seconds.
doc time to text number 4

Then the time will be converted.
doc time to text number 5

Tip: If you want to place the converted result in another place, you can click Kutools > Content > Convert Time > Convert Time to enable Convert Time dialog, choose a convert type, check Save to another location checkbox, and select a cell to place the result.
doc time to text number 6


Quickly and easily convert date to other date formatting in Excel

Have you ever tried to convert a date to day, month or year only? The formulas maybe hard to remember, but the Apply Date Formatting of Kutools for Excel can quickly convert a standard date to the date formatting as you need as below screenshot shown. Dont, waite, click for 30 days free trial!
doc apply date format
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (11)
Rated 4.25 out of 5 · 2 ratings
This comment was minimized by the moderator on the site
in the description is the wrong formula...
It is NOT: =TEXT(A1,"hh:mm:ss AM/PM")
It is: =TEXT(A1; "hh:mm:ss AM/PM")

with a semicolon, not a comma!
Rated 3.5 out of 5
This comment was minimized by the moderator on the site
I want to substitute time in the string, but whenever i use the formula the time converts into timevalue, i dont want time value. Can you please teach me what to do?[img]https://ibb.co/NVvknsF[/img]
This comment was minimized by the moderator on the site
Hi, Md. Shariful Islam, add TEXT function to fix the time format in the formula, the result will keep the time format but not the value.
=SUBSTITUTE(A1,MID(A1,13,8),TEXT(F1,"hh:mm:ss"))
See the screenshot:
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-substitute-time.png
This comment was minimized by the moderator on the site
Hi have a excel roster with time formulas to calculate the total hours for the week for each employee.

My question is on the days that staff member is OFF / Public Hol / Annual leave etc.
Wanting to enter words instead of time when someone is off or sick or on annual leave of if Pub Hol but when time in the words it messes up the time calculation giving a value error.

How do i enter words in a time cell without messing up the calculations.
This comment was minimized by the moderator on the site
Hi, could you upload a file or a screenshot to show what's your data structure like for me to better find a solution?
This comment was minimized by the moderator on the site
¡Excelente ! Gracias
Rated 5 out of 5
This comment was minimized by the moderator on the site
Thank You! I've been looking for a way to do this all day!
This comment was minimized by the moderator on the site
These formulas are super helpful, thank you. I find that I have a question about your Convert Time To Text String formula.I am using it to retain the hh:mm formatting in Excel into a Word doc via a mail merge.
This comment was minimized by the moderator on the site
Hiunable to get the formula in the excel
This comment was minimized by the moderator on the site
really helpful, thx!
This comment was minimized by the moderator on the site
Perfect. Thank you!!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations