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)
457 comments
Hi
Wanting to do 'end time' - 'start time' in order to get travel time.
End time and start time are in 24hr clock format.
For example, end time is 1100 and start time is 1000. Thus 60 minutes. But I need a formula for this.
No AM / PM or : used.
Please help! I've read the above.
Hi!
I recommend reading this guide: Calculate time in Excel: time difference, add, subtract and sum times.
Nevermind. I figured it out. Used LEN and RIGHT functions to add : to all values then I used the formula you presented above.
How to convert this data (1 Day 1 Hour 10 Minutes) into decimal
Hello,
Wondering how to do a column conversion of hours into days. I have 728 rows in the column
Hi!
Divide the number of hours by 24
Hello,
Is there a way to have excel automatically convert the time in hours to minutes, AND display the result in the same cell in which the data is input?
Thanks
Hi!
You can convert data in the current cell using a VBA macro.
Hi,
Is there a way to convert a length of time in text string formatted like the following to a numerical number such as total days, so I may determine the average length of that time for hundreds of entries.
10 Years 8 Months 26 Days
1 Years 4 Months 23 Days
5 Years 11 Months 5 Days
0 Years 6 Months 12 Days
Thank you for any assistance!
Hello!
I recommend reading this guide: How to get date difference in days, months and years.
This should solve your task.
I think I have the same issue, but with hours. Our report gives us the cell with: 7 hours 10 minutes 4 seconds, but we need it in the format hh:mm:ss. Cand we transform that?
Thank you
Hi! Extract numbers from text as described in this article, then use the TIMEVALUE function to convert text to time.
=TIMEVALUE(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$50),1)),MID(H1,ROW($1:$50),1)," ")))," ",":"))
Hello!
I could use your expertise, please!
I'm trying to go from:-
1 hour 18 mins
to
78 minutes
Is that possible?
Thanks
Stephanie
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question
=HOUR(A1)*70+MINUTE(A1)
Hey, I'm trying to convert 61:20:00 to decimal, but the formulas I'm finding online are not working because it's over 24 hours. Any tips on how to get it to work? I'm using Google Sheets.
Hi Rowan,
Please see this article: Convert time to decimal in Google spreadsheet
You can also check solutions here (including the comments): Calculating time in Google Sheets
Awesome! Found the solution in the first link. Thanks so much.
i have looked in the posts and can not get the following to work out. in column a i have "start time", b is "end time", c is "duration"and in column d i need it to be in minutes. ie - (start) 11:08 AM, (end) 3:20 PM, (duration) 4:12 - but can not get the results in minutes. Which formula do i need to convert duration to minutes?
my formula for duration is b3-a3 (formula is hh:mm
Hi!
I hope you have studied the recommendations in the tutorial above. Pay attention to the following paragraph of the article above - How to convert time to minutes in Excel
Hi,
I've received an excel sheet with the format of '# days # hours # mins' per cell in one column. Some cells have '# hours # mins' and others have just '# mins', all in the same column. I'm really stuck on how to convert all into mins without calculating each cell individually. Is there a formula I can apply to update this? Would really appeciate any direction!
Hello!
If I understand your task correctly, the following formula should work for you:
=IFERROR(--LEFT(A1,SEARCH("days",A1)-1),0)*1440+ IFERROR(--MID(A1,SEARCH("hours",A1)-3,3),0)*70+ IFERROR(--MID(A1,SEARCH("mins",A1)-3,3),0)
I hope my advice will help you solve your task.
Hi Alexander,
Thank you so much - it's working, I only need to add a '0' before the data that reads as '2 hours 30 mins' etc, so it's '02 hours 30 mins', otherwise it won't work on those cells. But this is wonderful, thank you! so much faster than what I was working on before!
Hi Amy, is there a formula that could fix this? Running into the same issue
Hello Alexander, just wanted to say the above helped me so much!!! I searched many forums and articles and you were the only one that had the answer. Thank you
Hi,
I need to show whether the time elapsed between 2 dates is within the required time frame.
I have generated the different between the 2 dates using:
=INT(W4-C4)&" days "&TEXT(W4-C4,"h"" hrs ""m"" mins """)
And then want to use a simple IF formula to determine if it's within the time frames:
=IF(Y4>K4, "No", "Yes")
where
Y4 is the cell with the first formula
and K4 is either 0 02:00, 1 00:00 or 5 00:00 (in custom format dd hh:mm)
This seems to work fine for the 2 hours and 1 day option but "Yes" is always return for the 5 day option.
I've also tried this where the first formula is a simple =W4-C4 and i have the same issue.
Can you help please?
Hello!
The formula in cell Y4 returns text. Therefore, you cannot compare text value with > or < .
In cell Y4, enter the time in the time format. For example, 2 days is 48:00:00.
Remember that date and time in Excel are numbers.
Try this formula
=IF((W4-C4) > K4, “No”, “Yes”)
I hope my advice will help you solve your task.
Hi, I'm having a classic problem with a graph being messed up because I have midnight time in my data.
I solved this once year's ago, but have since forgotten and can't find the answer. Was it something to do with writing that midnight time as decimal number?
Hi!
Sorry, it's not quite clear what you are trying to achieve.
Hi,
I was wondering if anybody can help me with this:
In an Excel sheet, I have accumulated deducted minutes in cell D1 displayed like this:
146:50 (146 hrs & 50 minutes)
cell formatted as: [h]:mm
I want to display deducted time in 3 separate cells like this:
H10 G10 F10
18 2 50
(days) (hrs.) (min.)
** each 8 hours = 1 day deduction
Thanks in advance,
Jaafar
Hello!
To get working days, hours and minutes, use these 3 formulas:
=INT(D1*24/8)
=INT(MOD(D1*24,8))
=(D1*24-INT(D1*24))*60
Hope this is what you need.
Hi,
This is what I needed. Problem solved.
Many many thanks, Alexander.
Hi Hi!
Would you please assist me on a formula / function that can convert the following:
03Hrs15Mins06Secs to just the numbers eg. 03:15:06
34Mins04Secs to eg. 00:34:04
thank you so much!
Hello!
Use substring functions in Excel to extract text from cell:
=TIMEVALUE(IFERROR(MID(A1,SEARCH("Hr",A1,1)-2,2),"00") &":"&IFERROR(MID(A1,SEARCH("Min",A1,1)-2,2),"00") &":"&IFERROR(MID(A1,SEARCH("Sec",A1,1)-2,2),"00"))
This should solve your task.
Hi & thanks for your most excellent site, it has come in handy for me so many times!
I have a problem where I'm trying to calculate the difference between two datetimes like this
Start: 29/Nov/21 11:52 AM
End: 1/Dec/21 5:38 PM
Excel nicely converts the above when pasting in to the cells A2 & B2 as follows:
A2: 29/11/2021 11:52
B2: 01/12/2021 17:38
C2: =B2-A2 returns the difference, expressed by Excel as: 02/01/1900 05:46
D2: =int(C2) returns the days, expressed by Execl as: 2 (format cell to number)
E2: =MOD(B2-A2,1) gives me just the time, expressed by Execl as: 05:46:00
So, I can use the above to deterrmine days & hours. If I use 8 hours as a working day, 2 days equate to 16 hours, plus the 5:46, would equate to 21:46 hours:minutes.
1. I'm wondering if there's a better way than the above?
2. My second problem comes from having to account for weekends (non-working times).
I have no idea how to do this. I suspect I'd have to use a calendar?
Would really appreciate your thoughts.
Many thanks,
Derrick
Hello!
Try this formula in cell C2:
=B2-A2-NETWORKDAYS.INTL(A2,B2,"1111100")
The NETWORKDAYS.INTL function counts the number of weekends. In this formula, these are Saturday and Sunday.
I hope I answered your question. If you have any other questions, please don’t hesitate to ask.
Hi I need to convert the following:
The original data is 03 Hrs 11 Mins 00 Secs and i need it is look like this 3.11
I hope you can help me!
Thanks so much!
Hello!
If your value is written as time, use the custom time format
hh.mm
Hi! thank you, but i am still not getting the desired outcome.
Please assist.
Hi!
I’m not sure I got you right since the description you provided is not entirely clear. However, I’ll try to guess and offer you the following formula:
=TRIM(LEFT(A1,SEARCH("Hrs",A1)-1))&"."&TRIM(MID(A1,SEARCH("Mins",A1)-3,2))
If this is not what you wanted, please describe the problem in more detail.
Good day Alexander Trifuntov
I am trying to convert 30d21h45m into hours on excel using a formula, could you please help.
Hello!
Please try the following formula:
=LEFT(A1,2)*24+MID(A1,4,2)*1+MID(A1,7,2)/60
Looking to average out two columns of numbers based on hours and minutes to find the mean time. Looking for
whole hour and minute total. Help would be appreciated.
Count Average Time Total Time
3 0.479166667 34:30:00 (34 hours, 30 min, 0 sec) [H]:MM:SS turns into 1/1/1900 10:30:00AM
3 8:16:20 24:49:00
Hello!
This article describes how to show time greater than 24 hours.
hi. i have a problem. i want to convert 176hrs to 160hrs. the formula for 176hrs is 1/7/1900 8:00. can you please help me convert it to 160 hrs? i really need the solution asap. really appreciate it. thanks
Hello!
To subtract 16 hours from the time, use the formula:
=H1-16/24
Read more in this article: How to add & subtract time in Excel.
How to convert 300 minutes to 3:20:00? HH:MM:SS
Hi!
Excel cannot perform incorrect calculations. 300 minutes is 5 hours.
I recommend reading the first paragraph of the article above.
How do I convert 44,694:30 hours to 44,694.5? I can't find formula here.
44,694 hours and 30 minutes to convert in decimal.
Hi!
If you look closely, you will find the formula for converting time to decimal in this paragraph.
=A1*24 (where A1 is the time value)
FYI, make sure the cell yur formula is in is formatted as a number no general. i've seen this cause issues.