by The tutorial demonstrates different ways to convert time to decimal in Excel. You will find a variety of formulas to change time to hours, minutes or seconds as well as convert text to time and vice versa.
Because Microsoft Excel uses a numeric system to store times, you can easily turn hours, minutes and seconds into numbers that you can use in other calculations.
In general, there are two ways to convert time to decimal in Excel - by changing the cell format and by using arithmetic calculations or Excel time functions, such as HOUR, MINUTE and SECOND. Further on in this tutorial, you will find the detailed explanation of the first way and formula examples demonstrating the other technique.
How to convert time to decimal number in Excel
Overall, there are three ways to change a time value to a decimal number: arithmetic operation, CONVERT function or a combination of three different Time functions.
The easiest way to convert time to decimal in Excel is to multiply the original time value by the number of hours, seconds or minutes in a day:
- To convert time to a number of hours, multiply the time by 24, which is the number of hours in a day.
- To convert time to minutes, multiply the time by 1440, which is the number of minutes in a day (24*60).
- To convert time to seconds, multiply the time time by 86400, which is the number of seconds in a day (24*60*60 ).
In the following sections, you will learn the other methods of converting times to a decimal number in Excel.
How to convert time to hours in Excel
This section demonstrates 3 different formulas to convert hours from the standard time format (hh:mm:ss) to a decimal number.
Formula 1: Arithmetic calculation
You already know the fastest way to convert a time value to a number of hours in Excel - multiplying by 24, i.e. by the number of hours in one day:
=A2*24
Where A2 is the time value.
To get the number of compete hours, embed the above formula in the INT function that will get rid of the fractional part:
=INT(A2*24)
Formula 2: CONVERT function
Another way to perform the "time > hours" conversion is to use the following Convert formula:
=CONVERT(A2, "day", "hr")
Formula 3: HOUR, MINUTE and SECOND functions
Finally, you can use a bit more complex formula, whose logic, however, is quite obvious. Extract the individual time units by using the HOUR, MINUTE and SECOND functions, then divide minutes by 60 (the number of minutes in an hour) and seconds by 3600 (the number of seconds in an hour), and add up the results:
=HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/3600
How to convert time to minutes in Excel
The same three methods can be used to convert minutes from the standard time format to a decimal number.
Formula 1: Arithmetic calculation
To convert time to total minutes, you multiply time by 1440, which is the number of minutes in one day (24 hours * 60 minutes = 1440):
=A2*1440
If you want to return the number of compete minutes, utilize the INT function like in the previous example:
=INT(A2*1440)
You can view the results in the screenshot below:
Formula 2: CONVERT function
To do the "time > minutes" conversion with the CONVERT(number, from_unit, to_unit) function, supply "day" as the unit to convert from and "mn" as the unit to convert to:
=CONVERT(A2, "day", "mn")
Formula 3: HOUR, MINUTE and SECOND functions
One more way to get the number of minutes is to multiply hours by 60 and divide seconds by the same number:
=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60
How to convert time to seconds in Excel
Converting time to total seconds in Excel can be done in a similar fashion.
Formula 1: Arithmetic calculation
Multiply the time value by 86400, which is the number of seconds in a day (24 hours * 60 minutes * 60 seconds = 86400):
=A2*86400
Formula 2: CONVERT function
The formula is basically the same as in the above examples with the only difference that you convert the "day" unit to "sec":
=CONVERT(A2, "day", "sec")
Formula 3: HOUR, MINUTE and SECOND functions
Use the HOUR, MINUTE and SECOND functions like in the two previous examples (I believe at this point you don't need any further explanation of the formula's logic :)
=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)
Tips:
- If any of the above formulas returns a value formatted as time, simply change the cell's format to Generalto display it as a number.
- To convert time to a decimal number that represents the time in the internal Excel system, apply the General format to the cell. With this approach, 23:59:59 will be converted to 0.99999, 06:00 AM to 0.25, and 12:00 PM to 0.5. If an integer part of the converted number is greater than zero, it means that your cell contains both date and time values.
How to split date and time in Excel
As is often the case, your Excel worksheet may contain dates and times in one cell, while you want to split them into two separate cells.
Remembering that in the internal Excel system the date value is stored as a whole part and the time value as a fractional part of a decimal number, you can extract the date using the INT function, which rounds the cell value down to the nearest integer.
Supposing your original dates and times are in column A, the following formula will accomplish the separation:
=INT(A2)
To extract the time portion, subtract the date returned by the above formula from the original date and time value:
=A2-B2
Where column A contains the original date & time values and column B contains the dates returned by the INT function.
If you'd rather not have time values linked to the separated dates (for example, you may want to remove the date column in the future), you can use the following MOD formula that refers to the original data only:
=MOD(A2,1)
Tip. If the separated date and time values are not displayed properly, change the format of the new columns to Date and Time, respectively.
This is how you split date and time in Excel. If you want to further separate hours, minutes and seconds into individual columns, then use the HOUR, MINUTE and SECOND functions, as demonstrated in How to get hours, minutes and seconds from a timestamp.
How to spell time in Excel
Sometimes, you may need to convert time into the format that reads something like "# days, # hours, # minutes and # seconds". A good thing is that you already know all the ingredients of the formula:
- Extract days using the INT function;
- Extract time units with HOUR, MINUTE and SECOND functions, and
- Concatenate all parts in a single formula.
Having difficulties with figuring out a proper formula for your worksheet? The following example will make things easy!
Supposing you have the dates of upcoming events in column B beginning in cell B4, and the current date and time returned by the NOW() function in cell B1.
The formula to calculate the time difference is as simple as =B4-$B$1. Of course, nothing prevents you from subtracting the current date and time directly with =B4-NOW().
And now, let's make a countdown timer that would show how many days, hours, minutes and seconds are left until each event.
The formula to enter in cell D4 is as follows:
=INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes and " & SECOND(C4) & " seconds"
If you wish to get rid of 0 values, like in cells D6 and D7 in the screenshot above, then include the following IF statements:
=IF(INT(C4)>0, INT(C4)&" days, ","") & IF(HOUR(C4)>0, HOUR(C4) & " hours, ","") & IF(MINUTE(C4)>0, MINUTE(C4) & " minutes and ","") & IF(SECOND(C4)>0, SECOND(C4) & " seconds","")
All zeros are gone!
Note. When either of the above formulas refers to a negative number, the #NUM! error will appear. This may happen when you subtract a bigger time from a smaller one.
An alternative way to write time in words in Excel is to apply the following custom time format to the cell: d "day," h "hours," m "minutes and" s "seconds". No formulas and no calculations are required! For more information, please see Creating a custom time format in Excel.
Convert text to time in Excel
If your time formulas and calculations do not work right, time values formatted as text is often the cause. The fastest way to convert text to time in Excel is using the TIMEVALUE function.
The Excel TIMEVALUE function has just one argument:
Time_text is a text string in any of the time formats that Excel can recognize. For example:
=TIMEVALUE("6:20 PM")
=TIMEVALUE("6-Jan-2015 6:20 PM")
=TIMEVALUE(A2) where A2 contains a text string
As you see, the formulas with cell references and corresponding text strings deliver identical results. Also, please notice the left alignment of time strings (text values) in cells A2 and A6 and right-aligned converted time values in column D.
Convert time to text in Excel
Supposing you have an Excel file full of times formatted to look like "8:30:00 AM" and you want to convert them to the text format. Simply changing the cell's format to TEXT won't work because this would change your time values to underlying numeric representation of the time. For example, 8:30:00 AM will be converted to decimal 0.354166666666667.
So, how do you convert cells to the text format so that your cells still have the time in them? The answer is to use the TEXT function that converts a numeric value to text with the display formatting that you specify, for example:
=TEXT($A2,"h:mm:ss")
The screenshot below demonstrates other possible formats:
How to convert numbers to time format in Excel
If you have a list of numbers such as 1, 2, 3.5 and you want to convert them to a time format, for example 1:00:00, 2:00:00 or 3:30 AM, perform the following steps.
- Divide the numbers by 24 (there are 24 hours in a day). The formula can be as simple as =A2/24.
- Select the cell(s) with the formula result, right-click and select Format Cells from the context menu or press Ctrl+1. Either way, the Format Cells dialog will appear, you select Time on the left pane under Category, and choose the format you want on the right pane under Type. Please see How to format time in Excel for more details.
If your time exceeds 24 hours, 60 minutes, 60 seconds, use these guidelines to correctly show it.
That's all for today. If someone wants to get the first-hand experience with the formulas discussed in this article, you are most welcome to download the sample workbook below.
If you want to learn a few more useful formulas to calculate times in Excel, please check out the related tutorials at the end of this page. I thank you for reading and hope to see you again next week!
Practice workbook for download
Converting time in Excel - examples (.xlsx file)
462 comments
Is there a potential solution for correcting the formatting. When Pasting time values from different systems they will show the minuets as hours and others will be correct. This is causing issues with my data tables.
I have a track result in a form of 2:04.66 (=2 minutes, 4 seconds, 660 milliseconds). What is the formula to convert the original value to seconds, i.e. 124,66?
Hi!
The answer to your question is in the first paragraph of the article above. Please read it carefully.
Multiply the time by 86400.
i have 400.5 min and to convert it into Hr Min Sec
how do i convert this (07-09-2021 21:00 ) into this (21:00 - 22:00)
Hi
Similar to Robert says:
February 6, 2021 at 11:37 am
Can I please have 1 days 4 hours 21 mins converted to 28.21 in Excel
I am importing travel time from google maps such as above so my cell contains let's say
16 hours 20 mins
Id like this to be 16.33 so I can charge it out multiplied by an hourly rate.
Love your help with this. (I'm actually using MS Access) if that makes it easier or harder?
Poida
Hi!
Why don't you use the recommendations of the first paragraph of this article?
I still can't get it, I can't see it in the first paragraph when it is all in just one cell
17 hours 59 mins
Thank you
Hi!
I am assuming that there is a text written in your cell and not the time. To convert this text to time, use the formula
=--SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(D1,ROW($1:$94),1)),MID(D1,ROW($1:$94),1)," ")))," ",":")
Thank you so much for your replies, I think you should go in my favourites.
That worked a treat, I just had to add the x24 to the end to get 17.98333 hours.
I'd still be at Uni trying to put that equation together, and I'm over 50.
Cheers Poida
Hi there,
How can I convert 12:00:00 AM to 00:00:00 and 12:00:00 PM to 12:00:00 in the same formula including both AM/PM, if I need to convert time to AM or PM accordingly?
I want to convert,
12:39:46 AM in A2 to 00:39:46.
12:04:14 PM in B2 to 12:04:14.
you can have formatted it into 13:00:00 instead
Hello!
Try using a custom date and time format.
I would like to convert 400 minutes to hours and minutes.... but I'd like to time displayed as 6 40 (where the 40 minutes are display using superscript.)
Hi I've been trying a few different formulas to convert from time to an hour decimal but they're all giving me the same odd results.
eg. for A1*24
7:35 is producing 559.58
10:00 is producing 562
I've made sure the format is decimal, and tried it in different ones but same issue. Any help would be awesome. thanks =)
Hi, how can I convert time 06:15 to 6.25? Thanks
Hi!
Have you tried the ways described in this blog post?
The answer to your question is in the first paragraph of this article.
Yes, but it doesn't work.
Well, the time that I need to convert is a result of calculated time. i.e. Start time is 9:00 (A1), end time is 18:30 (B1). I use the subtraction formula (=B1-A1) to get 9:30 (C1), and then I use the formula =C1*24 as stated in the first paragraph but it gives me 12:00 instead of 9.5. I am not sure what I did wrong in converting the 9:30 to 9.5. Please advise.
Hi!
If you want to count the time as a decimal number, then set the correct number format - decimal or General.
Got it, it works now. Thank you so much :)
When I type 05082021, I want it to show as 05/08/2021, and when I type 0957, I want it to show as 09:57
Please help. I've tried everything I can think of.
Hi!
Use a VBA macro
"1 day, 2 hours, 20 minutes and"
can you convert this to decimal?
I've used this formula:
=IF(INT(AF3-AD3)>0, INT(AF3-AD3) & " days, ","") & IF(HOUR(AF3-AD3)>0, HOUR(AF3-AD3) & " hours, ","") & IF(MINUTE(AF3-AD3)>0, MINUTE(AF3-AD3) & " minutes and ","") & IF(SECOND(AF3-AD3)>0, SECOND(AF3-AD3) & " seconds","")
and got this:
2 hours, 20 minutes and
How can I change it to decimal?
you help will be greatly appreciated.
All I need is a column to record date received and a column to record time received.
I just want to type 250621 for the date, and see it as 25/06/2021.
For the time I would like to type for example: 1315 and it show as 13:15.
I've tried the formula tab and formatting cells, but getting nowhere.
i have value 0.90 in a cell in excel i want to convert it as hours
Hello!
To get the number of hours as a decimal number, multiply by 24
Hi am new to all this and I have a question how do I convert 20hrs to numbers
Hi!
Read the first paragraph of this article carefully.
I have a column for duration. It varies from 22S to 17M 13S. Is it possible to create a formula to read this in 00:00:00 format? I can do something like =timevalue("0".&Left(C1,1)&":""&MID(C1,4,2)) for a duration of 2m 46s but that formula then wouldn't work for the next cell if it is 17m 59s. Is there a way to have one formula convert varying durations to the 00:00:00 format?
Hello!
If I understand your task correctly, the following formula should work for you:
=SUBSTITUTE(IF(ISNUMBER((SEARCH("m",C1))), "0:"&SUBSTITUTE(SUBSTITUTE(C1,"m ",":"),"s",""), "0:0:"&SUBSTITUTE(SUBSTITUTE(C1,"m ",":"),"s","")),"m",":0")
I hope my advice will help you solve your task.
It worked. Thank you.
I am creating a sheet to calculate how much time it will take to perform a series of daily tasks. I have a set number of items that require 7 minutes of work per item. I.e. I have a wo that states standard time to complete 1 = 7 minutes so in my mind if I utilize military time as a decimal 0.12 * 150 = 18 which is incorrect If I state a whole number i.e. 7 * 150 = 1050. How do I get this to calculate expected time to complete to show as time and be correct?
Kindly help. How do I convert 7h 27m 51s to HH.MM
Hi!
Have you tried the ways described in this blog post? Use example "Formula 3: HOUR, MINUTE and SECOND functions".
If this is not what you wanted, please describe the problem in more detail.
Hi,
Can you please help me:
2m 46s need to change in this form 00:02:46
thanks in advance
Hi,
For your data, you can use the formula:
=TIMEVALUE("0:"&LEFT(C1,1)&":"&MID(C1,4,2))
Pay attention to the following paragraph of the article above: Convert text to time using TIMEVALUE function
don't know if you can help but kevin asked the same question of 5th feb
I have task that need to be completed each different time but I am sure if I get the one calculation right it can be change for that length of time for example.
1 task takes 20 mins over a week they do 392 which in my head is 20 x 392 = 7840 minutes
my simple head is divide that by 60 to gat 130.67 but then if I use =CONVERT(M204,"mn","hr")/24 I get 5.44 that convert to 10 hours 40 minutes it seems which way I use a formula I get a different answer
the other times are
1 task x 20 mins
1 task x 30 mins
1 task x 75 mins
1 task x 45 mins
but I I can get the right formula changing the length of time should be easy …….. or will it
what is the right formula for this to give me the right length of time in hours and minutes for all these jobs
thanks in advance
Hello!
If I understood your problem correctly, then to convert minutes to hours use the formula
=A1/60/24
Then set the cell custom format to "37:30:55"
I hope my advice will help you solve your task.