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
now i add two man working hours but it not produce the correct answer
for example siva = 10.50 hrs
raja = 10.50 hrs
add 2 manhours = 21 hrs (excel result)
but i want 21.40 hrs
how do i do plz
Hi
I'm copying the data from the internet, the start time which in that format like "Fri Jan 11 20:30 19" and run time " 00:07:45:07" which is in "day:hour:minute:second" format. While pasting in to the excel sheet it's showing the show digit number like for start time 1547210301409 and run time value 27907397. Can u guys suggest me , How can i convert it in same format in excle ?
Hi
I want to show time without am/pm
how can I do it?
I'm trying to work out how to calculate time worked between 2 times, then minus 45 minutes break but then change the format from example 8hrs 45mins to 8.75hr
Our payroll department, works in decimals (1hr 15mins is 1.25hr, 30mins is 0.5hr.
I'm not sure whether that makes any sense or not.
It will make doing my guys timesheets so much quicker each week.
Many thanks
Phil W
How to convert any six digit no for example 71400 into 71° 40' 0" format in excel
Hello, Mohit,
Please do the following to change the formatting of your cells:
- Select a cell(s) for which you want to create custom formatting, and press Ctrl+1 to open the Format Cells dialog.
- Under Category, select Custom.
- Type the following format code in the Type box:
##°##'#''
- Click OK to apply and save the this format.
I can also recommend you to have a look at the following article on our blog to learn more about custom formats:
Custom Excel number format
Hey Honey,
I have 4, I want this to be converted into hours. Please guide.
Below are the more examples.
0
60
6.25
10
Thanks
Need help!!
I have a column with below details and i wanted to convert to time but i dunno what's going to be the divisor to properly convert to such.
0
30
100
130
200
Thank you so much.
Could you please help me with the following?
If I were to start production at 7:00am and had a work order to produce 500 cases which should take 2.5 hours to complete how do I calculate the end time in excel?
For example... What formula would I use to calculate the following?
Start Time: 7:00am (Cell A1)
Hours Needed: 2.5 (Cell B1)
End Time: 9:30am (Cell C1)
Thank you very much for your time!
I have the number 6.53 which actually is 6 hours and 53 minutes and i need to deduct this number (6.53) from 8 hours to get to know how much hours and minutes are not attended by the employee so in excel what formula will give me the desired result.
How do I convert 429:30 to 429.5?
Hi
Could you please help me with the following. My software returns an actual duration in the following way: 1 hour,30 minutes. This is returned in a single cell. However, I need to convert the output to 1.5 for example.
Could you please let me know how I could do this.
Many thanks
Vishal:
This will give you the result you want from your sample:
=LEFT(I29,SEARCH("Hr",I29)-2)&"."&((MID(I29,SEARCH("Min",I29)-3,2))/6)
Where "1 Hr, 30 Min" is in I29.
You'll have to work with this to get the results you want in another situation.
Hi,
Just wanted to let know others, I am able to find the formula (conditional formatting) as below:
For more than 9 hours: =CONVERT($D2,"day","hr")>216 Fill with color 1
For more than 10 hours: =CONVERT($D2,"day","hr")>240 Fill with color 2
For more than 11 hours: =CONVERT($D2,"day","hr")>264 Fill with color 3
Rgds,
Hi,
Thanks for the tutorial blog.
I have a sheet wherein I will be calculating the time spent by my team member.
Time-in will be enter in column B and time-out in column C, I will be calculating the total time spent in column D by formula =TEXT(c2-b2, "hh.mm").
I want to highlight the column D with one color, if the time spent is more than 9 hours and with another color, if the time spent is more than 10 hours.
What formula should I use in Conditional Formatting? Or is there any other way of highlighting the column D cells based on their values.
Thanks in advance.
Derok
Hi,
Just wanted to let know others, I am able to find the formula (conditional formatting) as below:
For more than 9 hours: =CONVERT($D2,"day","hr")>216 Fill with color 1
For more than 10 hours: =CONVERT($D2,"day","hr")>240 Fill with color 2
For more than 11 hours: =CONVERT($D2,"day","hr")>264 Fill with color 3
Rgds,
Hello, Nice Tutorial.
I have this , 1/3/1900 10:20:29 AM , in a a cell. I want to convert to sec 10:20:29. I don't want the date
Stamatis:
I would use the Text-to-Columns tool and in the third window select the date and the AM portions and click Don't Import option. Then format the data as Time in the Format Cells window.
Sir,
I want to convert a ten digit number to hh:mm:ss format for eg. 1505475418 to hh:mm:ss
Amit:
I found this explanation and conversions on Excel Tips from John Walkenbach.
If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch.
For example, here's the Unix timestamp for August 4, 2008 at 10:19:08 pm (GMT):
1217888348
To create an Excel formula to convert a Unix timestamp to a readable data and time, start by converting the seconds to days. This formula assumes that the Unix timestamp is in cell A1:
=(((A1/60)/60)/24)
Then, you need to add the result to the date value for January 1, 1970. The modified formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)
Finally, you need to adjust the formula for the GMT offset. For example, if you're in New York the GMT offset is -5. Therefore, the final formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
A simpler (but much less clear) formula that returns the same result is:
=(A1/86400)+25569+(-5/24)
Both of these formulas return a date/time serial number, so you need to apply a number format to make it readable as a date and time.
I've found that the custom format m/d/yyyy hh:mm:ss works pretty well. Be sure to make your cell wide enough to accommodate the size of the value otherwise you'll get ##### displayed in the cell.
I used the formula =DATE(1970,1,1)+A5/86400
After I do all of this your number turns out to be 9/15/2017 11:36:58
If you want just the 11:36:58 part you'll need to copy the 9/15/2017 11:36:58 and Paste Special/Values into another cell and copy the 11:36:58 into another cell. At this point it will be text, so if you want to do a DATEDIF or other calculation you'll need to use the first conversion for that.
we have cumulative overtime data in 45:00:00 format (actually in cell it shows like 01/01/1900 9:00:00 PM)
I want to convert it in number value like 45 (it must not in time format)
Nitin:
Have you tried to format the cell as a number?
It sounds like it is formatted as something else.
If formatting it as a number doesn't work try the custom format [H]:MM:SS
Sir,
I want to convert time (06:30) to number (0630) in excel.
Vikash:
I think the simplest way to convert a value that is already formatted as time in Excel is to copy the values from Excel and paste them into Word as plain text.
When they're in Word, replace the ":" with nothing, then copy these numbers and paste them into Excel.
When they're in Excel, format the cells as Custom format "0000" and you'll see the values displayed as you need them.
Al:
If you change the formatting to another Time that is included in the Time list, does this solve the issue?
I'm trying to calculate the difference between regular worked and overtime hours (hours over 40)
I have this formula to calculate the cell of up to 40 hours
=IF(E36=40,J36-40,0)
all I get is "0.00" or "####################..."
HELP!
I then need to multiply the product of these figures by the hourly wage.
Thank you
Al:
How is the cell formatted that holds the If statement?
This looks like a cell formatting issue.
Its a custom format [h]:mm