# How to convert time to decimal number, hours, minutes or seconds in Excel

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:

1. 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.
2. 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:

TIMEVALUE(time_text)

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.

1. Divide the numbers by 24 (there are 24 hours in a day). The formula can be as simple as =A2/24.
2. 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 apply or change the time format in Excel for more details.

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 for converting time in Excel.

If you want to learn a few more helpful formulas to add and subtract times, calculate elapsed time and time difference, please check out other parts of our Excel Times tutorial (the links are available at the end of this page). I thank you for reading and hope to see you again next week.

### 201 Responses to "How to convert time to decimal number, hours, minutes or seconds in Excel"

1. narasimha says:

nice tutorial

2. Scott says:

How do I return the second value from this number 00:00:24.306? When I use the Second(cell) all I get is 24. When I format the cell for 3 decimal places, I get 24.000. I would like to get 24.306.

• Hi Scott,

You can use the TEXT function, for example:
=TEXT(A1, "ss.000")

The result of the above formula is a text string. To convert it to a number, you can multiply the returned value by 1, and then format the cell for 3 decimal places:
=1*TEXT(A1, "ss.000")

• Joao says:

Hi
I have a similar problem with pivot tables. I have formatted all cells with "mm:ss,000" , but when the pivot is updated to sum all time cells, it doesn't consider the decimals ...
My use case is to sum all several time cells on a competition ... therefore decimals are important.
Can you help ?

Hi guys,

Thanks for the solutions you've shared. I found them very helpful, though I am not that 100% sure that I can get them right. I haven't tried yet some of them, and to what my concern is, I didn't find a solution to my problem still despite of all this shared solutions. Could anyone just direct me to a certain solution to my case?

My question was, how may I convert a given sample/answer (ex: 3days 10hours 35minutes) in a certain cell into a total minutes only (ex: 382719)?

In other words I just want to simply convert them into total minutes using 1 formula with the given sample above as: 3days 10hours 35minutes

Many thanks,

=INT(LEFT(A2; FIND("day"; A2) - 1)) * 24 * 60 + INT(MID(A2; FIND(" "; A2) + 1; FIND("hour"; RIGHT(A2; LEN(A2) - FIND(" "; A2))) - 1)) * 60 + INT(MID(A2;FIND(" ";A2;FIND("hour";A2))+1;FIND("min";A2)-FIND(" ";A2;FIND("hour";A2)) - 1))

4. Glenn says:

Hi,

How can I convert 42.81 into 43.21? I need to round the 42.81 into total of hours and minutes.

Thanks

• Hello, Glenn,

Sorry, we haven't quite understood your task. Could you give more details and examples?

5. Mirek says:

Great tutorial and so easy to understand and implement.

6. Chris says:

Can we convert the text "2 days 1 hour" or "3 hours 10 mins" to numerical number, measure by mins for example? Thanks.

• Hello, Chris,

=IF(ISERROR(FIND("day", A1)), 0, INT(TRIM(LEFT(A1, FIND("day", A1) -1))) * 24 * 60) + IF(ISERROR(FIND("hour", A1)), 0, INT(MID(A1,IF(FIND("hour",A1)-6<0,1,FIND(" ",A1,FIND("hour",A1)-6)),FIND("hour",A1)-IF(FIND("hour",A1)-6<0,1,FIND(" ",A1,FIND("hour",A1)-6)))) * 60) + IF(ISERROR(FIND("min", A1)), 0, INT(MID(A1, FIND(" ", A1, FIND("min", A1)-6), FIND("min", A1)-FIND(" ", A1, FIND("min", A1)-5))))

• Alex says:

Hello Maria,

This works perfectly besides in the cases where I am missing the hours in the cell (e.g. in the cell I have only "10 mins". I am not able to figure the issue out myself. Can you please help me?

7. Jamie says:

is there a formula to convert a date (MM/DD/YYYY)to a decimal format?

8. Femi says:

Please, suppose I have a particular time stated as 07:23 and I want to convert it to something like this: 07:15-07:30 and convert 08:11 to 08:00-08:15, how do I go about it?

Thank you

9. Am Kamal says:

Thank you so much for this valuable effort. Please I need your help in calculating the daily over time as i have 2 column for; "IN" (Hr:Min AM) & "OUT" (Hr:Min PM) and the regular working hours shouldn't be less than 7 Hours started from any time between 7:00 AM to 8:00 AM and finished at anytime between 2:00 PM to 3:00 PM if it is not within the regular range should be deducted from the overtime. Then to color the exceeded time in Cyan
Thank you so much

10. Manoj Sharma says:

Hi
Suppose i have some data in 40:15:00 Hrs, now i want in 40:15 Hrs, so would request you provide the formulas for this.

Thanks

• Somnath says:

try this one..I7 is your cell

=TEXT(\$I7,"hh:mm")

11. Somnath says:

try this one..I7 is your cell

=TEXT(\$I7,"hh:mm")

12. Shivaji says:

I Have some problem in Time diff calculation
office timing 9 to 19 Oclok , How to calculate over time.
working time 9 to 21.28 min .

Plz Suggest, In Excel calculation is 3.7 Hrs But actual it Is 3.5 Hrs . ( Converted In Numbers)

13. Raychel says:

My data pulls the exact time 00:00:00 format for when I receive a message. I need to combine them by hour so I can see how many messages I get per hour of the day, can you do that?

14. Matt says:

Working on a report where I have a measurement of time such as 112:26:56. This time is for the entire month, now how can I covert that time into a decimal?

15. karl says:

How do i change the time format from 10:00 into 10+00. (10 is the hr and 00 is the minutes.

• Hello Karl,

To change the cell format, do the following. Select the cell(s), press Ctrl+1 to open the Format Cells dialog, on the left-side pane under 'Category' choose Custom, and type the following format in the Type box: hh+mm

16. Brian says:

Hello,
I have a column for hours, another for minutes and another for seconds:
Hr Min Sec
7 15 22
7 17 25
7 17 45
How do I combine these into a single time formatted column?
Time
7:15:22am
7:17:25am
7:17:45am

Brian

• MEN says:

HI,
HOW DO I GET 1212 TO 12:12:00??

• KK says:

=TEXT(F7,"00\:00")

17. Cathryn says:

I am looking to do the following:
- calculate time from two given values, and convert into a decimal *that rounds to set criteria*.
We use a time calculation where
0-2min = 0.0
3-8min = 0.1
9-14min= 0.2 etc. so the criteria would have to be established.

Example:
START: 14:00 STOP: 15:03
=TIME: 1.1

18. John says:

great tutorial. My question is this. At my job I have to complete so many "points per hour" so if I have A1=7:00 and A2=17:00 and I have completed 72 points, which is located in A3. How would I calculate my points per hour? Thanks in advance

19. Greg says:

Hi there
I'm trying to convert 0d 1h 6m to a numerical number any chance of helping out I've tried to use a couple of variations to formulas on here but cant seem to get them to work any help would be appreciated.

20. Kieran says:

Our time is currently entered as 41:30:00 for 41 hours 30 minutes and 0 seconds. I tried using your convert time to hours formulas but I come up with 17.5 which is clearly not correct. Can you tell me what I am doing wrong?

• Manoj says:

Suppose your value is at cell 'A1' then use-
=INT(A1)*24+HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600

21. Paul says:

Hello, this has helped a lot, I have been able to calculate work units into total hours, but now I want to convert total hours calculated, 466:56 - into FTE.

Where 466:56 is the work units required to be worked in a month, and 168 hours is the amount of hours a staff member works a month so the FTE should be around 2.8

Dividing the cell with (=F11/189) 466:56 by 189 in does not work as the number in the "=F11" is shown as 19.46.

Hope this is clear, thanks.

22. Monique Grol says:

Hi,
I am trying to make a time-sheet for my working hours.
i have a column with time started and time finished. The 3rd column is the difference between the 2 (hours worked for that day). I do this for every day of the week (rows). In the next column I want to sum my hours that I worked per day, but I want to use a formula. In this column I don't want to use time, but a number. What formula do I need to use.
Example
Start Finish Hrs worked (time) Sum hours (numbers)
9:00 12:00 03:00 (=finish-start) 3 (=convert hrs worked)
9:00 14:00 05:00 (=finish-start) 8 (=sum hrs above+hrs today)
10:00 17:00 07:00 (=finish-start) 15 (=sum hrs above+hrs today)
08:00 18:00 10:00 (=finish-start) 25 (=sum hrs above+hrs today)

Thank you!
Monique

• Val says:

I am also looking for an answer to this - have you had any luck Monique?

23. Monique Grol says:

Hi,
I am trying to make a time-sheet for my working hours.
i have a column with time started and time finished. The 3rd column is the difference between the 2 (hours worked for that day). I do this for every day of the week (rows). In the next column I want to sum my hours that I worked per day, but I want to use a formula. In this column I don't want to use time, but a number. What formula do I need to use.
Example
Start Finish Hrs worked (time) Sum hours (numbers)
9:00 12:00 03:00 (=finish-start) 3 (=convert hrs worked)
9:00 14:00 05:00 (=finish-start) 8 (=sum hrs above+hrs today)
10:00 17:00 07:00 (=finish-start) 15 (=sum hrs above+hrs today)
08:00 18:00 10:00 (=finish-start) 25 (=sum hrs above+hrs today)

I have trouble finding a formula that can calculate the last column as time doesn't go further than 24 hours. i want to do this for the whole year.

Thank you!
Monique

24. George says:

Hi,

I’m trying to convert/separate the following date/time format “8/24/2016 17:05:36:242” in a workable format, ideally to numbers/values. I had various attempts: define various (custom) cell formats, using MOD function etc., but no success so far. Do you have any suggestions?
Thanks a lot.

George

25. Joshua says:

I am trying to add columns of hours, minutes and seconds. I was able to convert the columns into total seconds using =(C2-INT(C2))*86400. I then add the columns of total seconds together. I then take that value and divide by 86400. However when I try to convert my final value back into a time format, I am having trouble with cells that over 24 hours. I have tried using [HH]:MM:SS

26. Lisa says:

I am trying to convert clock in time from a 60 minute clock to 100 minute military time. How do I convert the minute portion of the time? For example: 7:52:03am should be 7:86:03am (I need to multiply the MM by 1.66)

• Hello Lisa,

To get it right and consider the AM/PM time, please use this formula:
=LEFT(TEXT(A1,"h AM/PM"),LEN(TEXT(A1,"h AM/PM"))-3)&":"&IF(INT(MINUTE(A1)*1.66)<10," ","")&INT(MINUTE(A1)*1.66)&":"&TEXT(A1,"ss AM/PM")

Where A1 is the cell with the original time.

• LEFT(TEXT(A1,"h AM/PM"),LEN(TEXT(A1,"h AM/PM"))-3) gets the numeric symbols of time including AM/PM.
• IF(INT(MINUTE(A1)*1.66)<10," ","")&INT(MINUTE(A1)*1.66) gets minutes with leading 0.
• TEXT(A1,"ss AM/PM") gets seconds with leading 0 and AM/PM.
27. Yanny says:

Hello, need help.
how to auto calculate the hours below the situation.
If have 12hrs kpi(key performance indicator)Before vessel arrival from the port of origin and then If the vessel arrived @ 02:30AM,8-29-15. what time supposedly ill sent my reports.

28. DIK says:

I FETCHED DATA IN EXCEL FORMATE FROM ONE PLACE AND I GOT 357564 AS BUSINESS ELAPSED TIME,WHICH IS DAYS,HOUR AND MINUTE..HOW WILL CONVERT 357564 INTO DAYS HOURS AND MINUTES

29. Ivan says:

Dear all,

could you give me one hint :)
I'd like to sum/add decimal number with general number.
Can I do it ? I was looking for but I couldn't find. Thank you!

30. John says:

Hi all,

I am trying to multiply x number by minutes and seconds and then get the result in days, hours and minutes.

For example, if I have 1100 units and each unit takes 4 minutes 50 seconds to produce, what formula would I use to determine that it takes x days, y hours and z minutes to produce the 1100 units. Not too concerned about seconds.

31. Orly says:

Hello guys,

Anyone could help me convert total time to decimal? Ex. 58:16hrs to be converted to 2d 10.26h.

Thank you.

32. JIm says:

I see an error in your formula for Converting time to hours in Excel

=HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/360

That last value should be 3600.
I found your tutorial but kept getting wrong values.
This is when I discovered that error.

• Paul says:

As Jim (post 32) has already noted in September, the formula to convert time to hours should read
=HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/3600, not 360! It does make a difference. Could you plse change that in the text.
Thanks, Paul

33. Pascal says:

Hi There,
I have calculated elapsed time, but rather than excel using ":" as time separator, I want to use "h" for hours and "m" for minutes. For example: 01:50 should be displayed as 01h50m. I want to keep it formatted as a time to be able to total up elapsed times later.
Thanks

• Nick says:

I discovered by experiment that you can use a backslash to escape the character you want to use as text in the format. So for your case the format field should contain hh\hmm\m. This works both in the custom format field for a cell and in a TEXT() function. For example, =TEXT(0.25,"hh\hmm\m") displays text 06h00m.

34. A.Razak says:

Hi everyone there
I need your help, to solve my problem
working hours in my workshop in 3 shifts
shift 1 starts 06:40 Ends 15:20 with break time from 11:45 to 12:20 total of 0.40 Min.
shift 2 starts 15:00 Ends 23:20 with break time from 19:00 to 19:20 total of 0.20 Min.
shift 3 starts 23:00 Ends 07:20 with break time from 03:00 to 03:20 total of 0.20 Min.
My quistion is how can i calculate the exact working hours for each shift without the break time hours by usiing excel formula.

35. Colleen says:

Hi all, I am trying to convert hours into day:hour:minute.
e.g. 208.03 and 852.47 to be converted into 28:2:14 and 115:7:13. Is anyone able to assist?

36. Cathy Moore says:

I am trying to convert 35.66 (this is 35 hours and .66 of an hour) into days:hours:minutes
The manual conversion is 1:11:40 (1 day:11hours:39.6 minutes)
is there a formula for this so that I do not have to convert manually?
Can anyone help with this?

37. Kris says:

Hello,
I need to calculate the duration of certain activities: start - stop = duration. The start stop cells are formatted: 10/18/2016 20:05. I need the difference in numeric form ( not text ). To make it harder there only 12 production hours per day. So, if production ends at 17:00 and restarts at 5:00 the task, for example, started at 13:30 and ended the next day at 8:00. The actual task time is 6.5 hrs. I don't want to include the non work time. I need the results in decimal format. Is this possible?

38. M says:

I am trying to convert Regular hours which is in format hh:mm to straight time hours, for example of 2.95 equals 2.57 becuase we divide 0.95 by 60 to arrive at 0.57. I tried using =Hours(cell) + Minutes(Cell/60), I get 0:00

39. Rizwan says:

Thank you so much for this valuable effort. Please I need your help in calculating the daily over time as i have 2 column for; "IN" (Hr:Min AM) & "OUT" (Hr:Min PM) and the regular working hours shouldn't be less than 7 Hours started from any time between 7:00 AM to 8:00 AM and finished at anytime between 2:00 PM to 3:00 PM if it is not within the regular range should be deducted from the overtime. Then to color the exceeded time in Cyan
Thank you so much

40. Saravana P says:

i have attendence machine report in excel where its mantioned 10.6 in general foramt when we convert that in to time its showing 2:06 how can we solve this problem kindly help

thanks

41. Scott says:

I have a big spreadsheet with times spent on a task stored in this format
dd:hh:mm:ss (days:hours:minutes:seconds)
The second column shows how many tasks completed in that time (usually in the thousands).
Is there a simple way to show how long is spent on each task?

Thanks

42. Samuel S Yeager says:

Is there a way to set an excel equation so that for time, one box says 0700 the next says something like 0930 and the net time box says 2.5 ?

43. timekeeper says:

hello, i'm adding the numbers of hours worked but when i autosum the values, it is not getting the right result. how to i add time values in decimal point? thank you

44. Daovon Latsena says:

Hi all professionals,
could you please helping me to calculate from number to be a time, ex: 7.15 to be 7:15 ?

Daovon

• Hi Daovon,

The first thought that comes to mind is using the Replace All feature to change all dots (.) to colons (:). As soon as you do this, Excel will interpret your entries as times.

45. Kannan says:

Hi All,
I try to calculate total hours and minutes for below list of values.
05:07:00 05:33:00 06:29:00 06:29:00 06:28:00 07:01:00
(Those values are placed from B3 column the G3 column)
I tried this formula
=MINUTE (SUM (B3: G3) /60) &":"&SECOND (SUM (B3: G3) /60)
Now I have got exact time, but there are some discrepancies in minutes.
As per the formula I need to get "37:07:00", but I used to get "37:10:00".
Kindly help some one to solve this issue.

Regards

Kannan Mohan

46. VAIBHAV says:

HOW TO COUNT TOTAL MONTH HR (MINES ATTEND... SHRRT DATE 26 OCT TO 25 NOV TOTAL 31 DAY ,SO HOW TO COUNT 31 DAY TIME

thanks

48. Elnur says:

Hi,
we have a daily reports and they are counted from 20:00 hours to 08:00 hours, I found a problem counting the hours,

for example if work started at 23:00 and completed at 06:00 hours, what formula shall I use to get right result?

xls. simply takes 23:00-06:00 = 17, which is not right,

I found a way but I believe the is must be a easier way??

Thanks

49. Peshiyaboy says:

Hi Elnur,

You can use below formula to count 24 hrs time:-

suppose your 1 time in column A and 2 time in column B then type-
=((b2-a2)+(b2<a2))

change number format to hh:mm by pressing ctrl+1.

enjoy...!!

50. Fazli says:

Hai All,

How to convent from number to time e.g.085600 to 8:56:00 AM

Thanks

51. alex says:

I have data that is formatted in the following manner.

"22 minutes" where the minutes are written as a number followed by the word "minutes" as well as "1 hr 30 min" where the time is larger than an hour and they are written with a number for the hour followed by "hr" and the a number for the minutes followed by "min". Is there a way to first separate the two different notations and then convert the numbers to a format hh:mm:ss? Any help would be appreciated.

Thanks!

52. Wilbur says:

How do I show only values and not "0"
examples
Time result 0:03:51 would show as 3:51
Time result 0:30:10 would show as 30:10
Time result 0:00:22 would show as :22 or 0:22

The current cell formula is
=TIME(N160,Q160*60,P160)
Thank you

53. Lakhvir Singh says:

Hi everyone,
How can I calculate agricultural land units, I have

20 Marla=1 Kanal
8Kanal=1 Acre (i.e. 1 Acre is equal to 160 Marla)

How to Add, Subtract, Multiply or Divide these units ?
kindly help

54. Deb3000 says:

How would you calculate an end time if the start time is 8am and the hours worked is 6.75?

55. Val says:

How can I calculate the time into hours?

Day In Out Regular Hours
1 7:13am 1:15pm

56. Chris says:

Anyone out there, can you help me, please?

I am trying to convert 21 hours 36 minutes to a decimal format in Excel, for a part-time weekly timesheet i.e. 21.6 hours, what formula do I need to use to achieve this?

57. Danish says:

Hi,

I have a Question for my offical report work.

i used this formula to =INT(I2-A2) & " days, " & HOUR(I2-A2) & " hours, " & MINUTE(I2-A2) & " minutes and " & SECOND(I2-A2) & " seconds" to get the exact days,hours,min,&sec . in this scenario i need to covert this values into percentage .

My task time is 5 hours if i exceed the limit the cell wants to show the exact percentage

eg1 : i received one mail on 22 jan 2017 12:14:20 pm and i replied to that mail on 22 jan 2017 20:41:20 pm . it exceed my task line so could you please help me what formula should i used to solve the issue.

58. Ashley says:

I need to convert minutes to individual units
column A has the date, column B has the start time column C has the end time

8-22 mins = 1 Unit
23-37= 2 Units
38-52 = 3 Units
53-67 = 4 Units

The Units are made so you can choose them from a drop box.
I need it so that the times = the correct number of units, if they enter the wrong units or no units it would highlight. Or else if the time equals 7 mins or less that the time is highlighted. PLEASE HELP

59. SAQUIB says:

How to write 15:30 into 15 hours 30 minutes?

60. SAQUIB says:

How to Write 15:30 into 15 hours 30 minutes?
i don't have any format like this in format cell- custom

• Simran says:

You can write (hh "hours" mm "minutes") in custom window, without the brackets(). I have written it just to make it look different from other text.

61. Abdul Rehman says:

Hi,
i am making attendance sheet and i am unable to get the result when an employee works less then the regular time ,so i need his hours in Negative numbers.please guide me how can i get negative numbers.i am using h:m format in excel 2013.
Best regards,
Abdul Rehman

62. Abdul Rehman says:

hi,
i wants to calculate over time .our organization start overtime after 30 minutes to the regular time. i am unable to set overtime formula .
Please guide me an Example ..i am using Excel 2013.
Best regards,
Abdul Rehman

63. Thiruppathi says:

when am (60+12)/60 I got answer as 1.20 hours but the correct answer is 1.12 hours how can I 1.12 hours.do u have any formula for than...please reply as soon as possible

64. Suresh says:

Dear Excel user's

I have entered time for 6.10 mins in Excel (i,e 6 hours and 10 mins) how to i get 310 minutes using the formula Pls help us.

• Dear Suresh,

please note that 6 hours and 10 mins is 370 minutes. But if you need 310 minutes exactly and 6:10 is stored in A1, please use the following formula:
=(A1*1440)-60

65. rats says:

Hi Team,

Am trying to caluculate the time difference in same but am unable to do that, could anyone help me on the same.

09:00 AM-06:00PM in one cell another i need the number of working hours

66. Arun Kumarr says:

Thank you. You saved my day.

67. Tanguy says:

Hi,

Some question,
-I have in line A 2 shifts Morning & Evening( with end & start times)
- Line B gives my the worked hours for AM & PM( end time - start time)
- Line C should give me the total hours ( 27 : 00 ) but instead it always give me the time 03:00.

Any workaround

A: STU 1 3:00 - 23:00 18:00 - 1:00
B: TOTAL 20:00 7:00
C: DAY TOTAL ???03:00?????

68. SG says:

Question: How to solve Date and Time in Excel formula if start one day and end next day?

I have issue if I have

Start time: 7/7/2017 23:28:00 (Time start at evening PM) and
End time: 7/8/2017 00:00:00 (Time end next day in the morning AM)
I want to subtract End Time to Start time but not getting correct result.

69. SG says:

How to convert "22.91" in Minute and Second?

70. mizan says:

Hi

71. Hobart says:

How do you separate elapsed minutes or seconds with thousands separator? I have calculated times between two dates and custom formatting will not let me separate the minutes or seconds accordingly.

Thanks

72. Virginiah says:

Kindly assist me on how to display 130 minutes as integer in excel program

73. hyewiz says:

Please help me I need to change 1.50 to show 1.30, or 1.25 to 1.15 how can I do that

74. Rafi says:

HI Team,

Please let me know how to convert minutes to hours for example I need to convert 120:33:00 to hours & minutes.

• Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

75. Kalpesh says:

I want to IN-OUT Time Calculation formula for my company Register in soft format.

Worker come in 8 Hrs shift = P

Night + First (next day) = P
8 Hrs + Duty = P + OT Hrs

required formula as above condition.

IN Date IN Time Out Date Out Time Preset OT Hr
Absent
01-10-17 6:30 01-10-17 18:30
01-10-17 22:30 02-10-17 8:30

• Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.

76. aleem says:

Hello Guys
i want formula that if i put 1430 ,it should convert to 14:30. plz help

• Doug says:

Aleem:
There are several ways to do that, here is one:
Where original number is in A2 enter this into A3:
=TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),)
Then format A3 in the time format you want to see.

77. Jeff says:

I would like to convert "39 hour(s), 6 min(s), 31 sec(s)" to a decimal

78. Winston Van Der Merwe says:

I’m wanting to display 12:34 as 12.34.

• Doug says:

Winston:
Let's say you want the original number in cell A2.
Before you enter anything into A2 format the cell as text.
If you want the modified number in B2 then format B2 as text before you enter:
=SUBSTITUTE(A2,":",".")

Hi hope you will be fine,

i want to convert number to time format and i used this formula =left(A3,2)&":"&right(A3,2) and its giving me correct time format on below number 1234,1232,3145,

i want to know how to convert these number exe, 0123,0234,0012,0020,0003.

kindly help me out in this regard

• Name says:

=IF(LEN(A3)>2,LEFT(A3,LEN(A3)-2),"0")&":"&IF(LEN(A3)>1,RIGHT(A3,2),"0"&RIGHT(A3,1))

80. Biswajeet says:

How do I convert 1:45:87 (minutes: second: 100ofsecond)
The result should be in second ex (105.87)second
But unable to enter because its automatically takes 1:45:87=1:46:27 please somebody help

81. al says:

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

• Doug says:

Al:
How is the cell formatted that holds the If statement?
This looks like a cell formatting issue.

• al says:

Its a custom format [h]:mm

82. Doug says:

Al:
If you change the formatting to another Time that is included in the Time list, does this solve the issue?

83. VIKASH says:

Sir,

I want to convert time (06:30) to number (0630) in excel.

• Doug says:

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.

84. NITIN says:

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)

• Doug says:

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

85. Amit Gupta says:

Sir,
I want to convert a ten digit number to hh:mm:ss format for eg. 1505475418 to hh:mm:ss

• Doug says:

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.

86. Stamatis Pierros says:

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

• Doug says:

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.

87. Derok says:

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.

Derok

• Derok says:

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,

88. Derok says:

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,

89. Vishal says:

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

• Doug says:

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.

90. Hem says:

How do I convert 429:30 to 429.5?

91. Ahmed says:

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.

92. Jordan says:

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!

93. Aung Kyaw Lynn says:

Thank you so much.

94. Daryl says:

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

95. Asif says:

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

96. Mohit says:

How to convert any six digit no for example 71400 into 71° 40' 0" format in excel

• Hello, Mohit,

- 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

If you have any questions, don't hesitate to ask.

97. Philip says:

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

98. Saman says:

Hi
I want to show time without am/pm
how can I do it?

99. Gaurav says:

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 ?

100. RAJA says:

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

60+ professional tools for Excel