How to Convert Time to Number in Excel

294 comments to "How to convert time to decimal number, hours, minutes or seconds in Excel"

Derrick Robinson says:

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.
Melandri Van der Wal says:

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
  - Melandri Van der Wal says:
    
    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.
Karabo says:

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
Bernard Kraft says:

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.
sally says:

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
Rhea Al says:

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.
Bipin Patel says:

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)
  - BCConsulting Inc. says:
    
    FYI, make sure the cell yur formula is in is formatted as a number no general. i've seen this cause issues.
Josh says:

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.
nancy says:

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.
rakesh says:

i have 400.5 min and to convert it into Hr Min Sec
Ravi says:

how do i convert this (07-09-2021 21:00 ) into this (21:00 - 22:00)
Poida22 says:

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?
  - Poida22 says:
    
    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)," ")))," ",":")
      - Poida22 says:
        
        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
Zen says:

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.
- Deku says:
  
  you can have formatted it into 13:00:00 instead
Bill Romano says:

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.)
josh says:

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 =)
Judy says:

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.
  - Judy says:
    
    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.
      - Judy says:
        
        Got it, it works now. Thank you so much :)
Lee says:

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.
Rain says:

"1 day, 2 hours, 20 minutes and"

can you convert this to decimal?
Rain says:

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.
Lee says:

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.
Iqbal Hussain says:

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
Salim says:

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.
Brandon says:

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.
  - Brandon says:
    
    It worked. Thank you.
Carl says:

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?
Lester says:

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.
matija says:

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
Allan says:

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.
Robert says:

Can I please have 1 days 4 hours 21 mins converted to 28.21 in Excel
- Hi,
  Please describe your problem in more detail. How is your original data written as 28:21:00 or as the text "1 days 4 hours 21 mins"?
Taz says:

Hi There,
I need to convert 08:30:24 into hours and minutes only. tried with above but failed, Kindly help
THanks
Stay safe.
- Hello!
  If I understand your task correctly, you can use custom time format —
  
  "hh:mm"
  
  I hope it'll be helpful.
Koopall says:

How to convert 56 to 056:00 in Excel
Thnx
- Hello!
  If I understand your task correctly, the following formula should work for you:
  
  =TIME(0,56,0)
  
  Use a custom date format
  
  [hh]:"0"mm:ss
  
  I hope this will help
Medo says:

Hi there,

Is it possible to convert time 08:30 to 08.30 instead of 08.5

Thanks,
- Hello!
  Time cannot be shown using a point. But you can convert it to text
  
  =TEXT(A1,"hh.mm")
  
  I hope my advice will help you solve your task.
Sam says:

Hi,

How could i convert shift times in to hours as a formula.

For example, if someone is working from 6am - 2 pm 5 days a week, how can I get the total hours worked calculated?
- Hello!
  This question has been asked many times. For example, here.
  
  I hope it'll be helpful.
Haley says:

I am trying to keep track of my employees hours worked in Excel. I do not need to calculate their "In" and "Out" times, but instead calculate their bi-weekly total hours to ensure they do not go over their allotted 69 hours per month.

Example:
Debra worked 31.45 hours for pay period 1 of 2.
Right now I have =69-31.45 to show me how many hours she has leftover for the month. However, it is giving me 37.55 - which is incorrect.

How can I change the whole number in Excel from 100 to 60 so it will calculate her remaining hours?

I hope this makes sense! I have been on a hunt to figure this out - YouTube, Google, my Excel book and I cannot figure this out.
Any help with this is SOOOO appreciated :)
Keyland says:

I'm trying to take a Start Time and add 1 minute per question being answered to calculate an end time.
What I currently have:
(F13) Time Format (HH:MM) Start Time: Manually enter Time "example. 21:00"
(G13) Number Format Total Questions: =(Results!K3) "example. 153"
(H13) End Time: =SUM(TIMEVALUE(F13:G13)) "Result: Error Value"

Could someone help me resolve the error?
- Hello!
  If the number of minutes is written in cell G13, then you can add minutes to all of them with the formula
  
  =F13 + G13/1440
  
  I hope I answered your question.
Lisa Sheehan says:

How do you do the first example you shared if the time is greater than 12? for example, I have time 23:56
and I would like to convert it to minutes. When I use this formula =(R11*1440) I am getting 14396 - this should be 1436. Why is this happening? Thanks!
- Hello!
  My result is 1436. Check cell R11, you may have entered the date and time, not just time.
Rahul says:

I want to convert minutes in HH:MM:SS format but condition is minute showing in General

For Example

Total min 200 is nothing but 03:20:00

Is there any formula to convert like mentioned above?
Robert Scott says:

I need to get the year, month, and day as separate integer values from a date such as 7/13/2014.

How do I do that?
- Hello!
  Please check out the following article on our blog, it'll be sure to help you with your task: How to use YEAR, MONTH, DAY functions
  Hope you'll find this information helpful.
Sheldon Zaccanti says:

am try to convert actual minutes to decimals such as 4.11 hours worked to equal 4.18 hours and 3.32 hours to 3.53 and so forth so it works like a time clock where 15 minutes =.25 -30 min =.5 45 minutes =.75 thank you in advance
- - Robert Johnson says:
    
    I am also having a problem like this. Due to a disability i need to do my my timesheet in excel. we use 24 hour time (military) but they want the difference expressed as a decimal. For example, Start Time 11:30 End Time 16:45 = 05:15 but they want the time sheet to say 5.25 (don't trust my math) I'm sure you addressed it earlier but if you did I didn't get it. I would appreciate your guida
Youssef Ouazzani touhami says:

bonsoir,
j'ai un petit problème sur excel, c'est que j'ai besoin de créer des colonnes avec ces donnée

- column 1: Julianne day (1 to 365)
- column 2: Solar time in decimal format (00:00:00) with interval of 5 min (from 1 to 12 for each hour))

mon problème c'est que j'arrive pas a trouver une formule pour gérer l'intervalle de 5min sur un ans.

merci
Anees says:

August 1, 2020 3:52:15 PM AST
how to change this one as date format , tried a lot, can you please guide me.
- Hello!
  You haven't written which format you want to change. You can do this if you read this manual on date formats.
Sudhakar says:

2 days 19 hours 40 minutes

Its convert to 67.40 Dec... Can u plz send the excel formula
- Hello!
  What does 67.4 mean? What are these units of measurement?
  2 * 24 + 19 + 40/100? This number doesn't make sense.
deepak says:

i have 261 minute. needs to be convert as HH:MM
- Hello!
  The formula below will do the trick for you:
  
  =CONVERT(D1,"mn","hr")/24
  
  Set the cell time format hh:mm
Shelley says:

Hello;
Can you please help convert decimal 37.5 to hours and minutes.
When converting a number under 24 hours the formula number/24 works but if totalling hours over a week, the formula does not work if the total goes over 1 day or 24 hours.
Thank you
- Shelley says:
  
  I am looking for the excel formula please :0)
- Hello!
  To be able to show more than 24 hours in a cell, please use this time format: "37:30:55". You can find it in the list of Excel time formats.
libbrecht says:

Hi,
How to convert 9,30 in decimal 9,50 or 8,45 in 8,75.
The times I receive in Excel are the effective times marked with a comma (none :)
tnx
- Hello!
  The rounding of numbers in your examples occurs according to different rules. Therefore, I can not offer you a general formula for your two tasks. I recommend paying attention to the MROUND function. For your first example, the formula is suitable
  
  =MROUND(A1,0.5)
Erica says:

I would like to convert time to a numerical number to add up total hours.
Example:
6:00AM-2:00PM=8 hours
- Hello Erica!
  Specify what you want to calculate. Your formula counts the time from 2 PM to 6 AM. And this is not 8, but 16 hours. If you want to calculate the time from 6 AM to 2 PM, then the formula is necessary
  = 2: 00 PM-6: 00AM
  To convert a result from time to number, use the formula
  = (2:00 PM-6:00AM) * 24
  And set the number format in this cell
Yudi says:

Hello dear..
I am from Indonesia..
How to stop auto convert number from general to custom
ex.
from 1. auto convert to 01.00
from 10. auto convert to 10.00
from 100. auto convert to 100.00.00
Santiago Grullon says:

How do you convert 06:55 into 0655 in Excel. I want to to eliminate the colon.
Thank you.
- Hello Santiago!
  You can convert time to text.
  Please try the following formula:
  
  =TEXT(A1,"[hh]mm")
  
  Learn more about the TEXT function on our blog.
Geoff says:

I've downloaded a csv file that has a time on it but when I convert to Excel and use a formula on it it shows as the decimal equivalent of the number and not the time ,I cannot get to change to a time format that equals the original downloaded number/time
- Hello Geoff!
  Please try the formual below to convert time from a decimal number to "hh:mm:ss":
  =TIME(D1, MOD(D1, 1)*60, 0)
  
  That should do the trick.
  - Geoff says:
    
    thank you but the issue I have would be say i wanted to concatenate 2 cells ..... one with contents CPark and the other 10:46 AM the concatenate function results CPark0.4486111111111111 rather than CPark10:46
    your suggestions effect upon the original 10:46 AM cell returns 12:26AM
    thank you for your help so far though
    - Hello Geoff,
      
      Thank you for the clarification. In this case, formual below will convert your data from time to text:
      =CONCATENATE(A1, TEXT(B1, "hh:mm AM/PM"))
      
      That should do the trick.
      - geoff says:
        
        sorry that is not the answer thanks Alexander although its probably on the right track
        I cannot find the solution anywhere on the internet
      - geoff says:
        
        working now thank you Alexander !!!!
        
        Geoff says:
        
        so when I use a look up to cross reference 2 sheets in the same workbook even though I have the concatenate correct it doesn't pick up the cell I need ...can anyone have a look at the file if I send it to them to see where Im going wrong please ? I'm sure its due to the time format mentioned above that even though they look the same in the cell ref they are not .....
Danielle Parizeau says:

Hello,
In my excel spreadsheet I have column totally the number of hours in total (ex: from (9 am to to 10:30 am = 1.3) I would like to convert the 1.3 to 1.5. How add this function in my existing formula: =IF(k3≥J3,K3-J3,1-K3+J3)*L3..... L3 being the hours in total.
Thank you!
- Hello Danielle!
  If I understand your task correctly, you need to convert time from HH:MM:SS format to decimals. If so, you just need to multiply it by 24 (for example, =А3*24) and set the General format for this cell.
Maty says:

Has anyone actually found a resolution conversion for "D HRS:MIN:SEC" to "HRS"?
eg. 1 22:12:00
Dave Chladek says:

I know I'm asking a lot here but could you explain how to convert "Duration in seconds (Unix)" to a readable format of years,months,weeks,days,seconds in Excel? I see all sorts of ways to convert date to date, etc but not "duration" of time in seconds! I need a way to convert it into a readable format of years, months, weeks, days, hours and seconds. I can do it in Perl, C and others but not in Excell. If you can help I have a few examples of what the end result needs to be in specific representations such as (11W) for 11 weeks or (13W2D) for 13 weeks and two days or (1Y26W6D) 1 year, 26weeks, 6days and even further. Here are a few examples...

Duration in Seconds:
1) 6652800 and the output would be 11W or 11(W)eeks
2) 8035200 and the output would be 13W2D or 13(W)eeks 2(D)ays
3) 47779200 and output would be 1Y26W6D or 1(Y)ear 26(W)eeks and 6(D)ays
4) 47779200 seconds and output would be 1Y6M5D3H16M51S or 1(Y)ear,6(M)onths,5(D)ays,3(H)ours,16(M)inutes,51(S)econds
and/or convert years into months...
5) 47779200 output to 18M5D3H16M51S or 18(M)onths,5(D)ays,3(Hours),16(M)inutes,51(S)econds

Does this make sense? I'm not in a big hurry so if this is to much at this time don't worry about it. I'll keep looking around and I want to say: I really appreciate your taking the time to help with your explanations and this web site, these examples and explanations some of my make daily stuff much easier and I really appreciate how you offer so much expertise to everyone.

Many Thanks!!
Dave C.
JERRY says:

11.2hrs convert to days,hrs,min
formula pls?
Naresh says:

If Hour minute and second value 1370:06:09 in this format then how to separate hours minute and second.
Pls suggest.
akashsaini says:

how to convert in date & Time format below value
20001124204143.0Z
- akashsaini says:
  
  I will try with Mod formula but 2020 value showing wrong this will shows in filter as 2020 Jan to Dec but still running feb month and when we check in Ctrl+Shift+3 format in 2020 date then showing another date.
Sebastian says:

Hello everybody!
I can't find within the answers … I'm having difficulties with changing minutes and second in format 35:12 into seconds. Result I am looking for should be 2112.
Thanks"
Chai Yang says:

hour calculation - after sum total hour is 18:90: I want to I change from the 18:90 (18 hrs 90 min) in order to become 19 hrs 30 min. Can you I change it in Excel?

thank you
Hastings Wauka Mwale says:

Start 6:45 7:28 7:00 7:27 6:28 6:59 13:00
Finish 22:48 23:00 23:35 22:15 22:54 22:31 22:03
H:min 16:03 15:32 16:35 14:48 16:26 15:32 9:03
How do we add these hours and mins to hours only in Excel?
Brian says:

Trying to convert to below to minutes with out text

Notify to Last Assign Transit Time Total In Progress Time Turnaround Time
1h 14m 1m 40m 58m
Michael says:

Creating an Excel spreadsheet to use as a time card to keep track of the hours worked, I have used the formula =IF(end>start, end-start, 1-start+end) to reflect there are times I'll elapse (i.e. - go beyond midnight) to calculate the total number of hours. Now I need to convert that time into an hour and decimal minutes format to be able to calculate regular pay and overtime pay. How do I do that?
SUKHVINDER KAUR says:

I WANT TO ADD TIME VALUE AS U/M:-
A1 = 93:01:00
B1 =13349:50:00
C1 = REQUIRED FORMULA TO ADD ABOVE MENTIONED TIME VALUE.
THANKS
Dilip says:

Sir,

Date Shift ArrTim LateHrs DepTim EarlHrs WrkHrs OvTim Present

01/12/2019 G 0.00
02/12/2019 G 9.56 18.00 8.04 1.00
03/12/2019 G 9.56 18.01 8.05 1.00
04/12/2019 G 9.46 18.01 8.15 1.00
05/12/2019 G 9.57 18.01 8.04 1.00
06/12/2019 G 9.47 18.18 8.31 1.00
07/12/2019 G 9.57 18.00 8.03 1.00
08/12/2019 G 0.00
09/12/2019 G 9.47 18.00 8.13 1.00
10/12/2019 G 9.56 18.01 8.05 1.00
11/12/2019 G 9.57 18.00 8.03 1.00
12/12/2019 G 9.47 18.00 8.13 1.00
13/12/2019 G 9.58 18.01 8.03 1.00
14/12/2019 G 10.26 0.26 18.00 7.34 1.00
15/12/2019 G 0.00
16/12/2019 G 10.05 0.05 18.01 7.56 1.00
17/12/2019 G 9.56 18.00 8.04 1.00
18/12/2019 G 9.56 18.00 8.04 1.00
19/12/2019 G 9.57 18.00 8.03 1.00
20/12/2019 G 9.57 18.01 8.04 1.00
21/12/2019 G 9.58 18.00 8.02 1.00
22/12/2019 G 0.00
23/12/2019 G 9.56 18.00 8.04 1.00
24/12/2019 G 9.57 18.00 8.03 1.00
25/12/2019 G 9.57 18.00 8.03 1.00
26/12/2019 G 9.55 18.00 8.05 1.00
27/12/2019 G 10.06 0.06 18.00 7.54 1.00
28/12/2019 G 9.53 18.00 8.07 1.00
29/12/2019 G 0.00
30/12/2019 G 9.58 18.00 8.02 1.00
31/12/2019 G 9.58 17.09 0.51 7.11 1.00

kindly solve this how we calculated the total working hour's in that sheet
here is mentioned that in time and out time and difference how many hour's working an employee in our office and after how to calculate total working hour's in a month kindly reply

Thanks.
Shahid says:

Please support how to convert time into hours duration i.e. OUT Time 10:23:28 AM and IN Time 7:27:31 PM need to calculate the duration in Excel for bulk data.

Thanks.
- Hello Shahid,
  If you need the time difference in hh:mm:ss format, here is the formula for you:
  =IF(A2<A1, A2+1, A2)-A1
  
  Supposing the IN time is in A1 and OUT - in A2. If however, you need it to be a just number, the following formula will do the job:
  =HOUR(IF(A2<A1, A2+1, A2)-A1)+MINUTE(IF(A2<A1, A2+1, A2)-A1)/60
Jitin Kaushik says:

How can convert 1 productivity = 8:30 hours in excel sheet example .5 = 4:15hours and .25 = 2 hours
Time difference 15,30,45,60 minutes
GIREESH KUMAR says:

I WANT TO CONVERT HOURS TO minutes
A/S CAST OFF TOTAL HRS TOTAL minutes
15:50 19:25 3:35
Khatijah Nasir says:

How to format hours by using IF...
Let's say I want from 06:00am to 22:00pm be "morning" and 22:00pm to 06:00am be "night".
Thank you.
- Michael says:
  
  Creating an Excel spreadsheet to use as a time card to keep track of the hours worked, I have used the formula =IF(end>start, end-start, 1-start+end) to reflect there are times I'll elapse (i.e. - go beyond midnight) to calculate the total number of hours. Now I need to convert that time into an hour and decimal minutes format to be able to calculate regular pay and overtime pay. How do I do that?
endy says:

Hi all,

how to split 1 day 3 hours 17 minutes into hour and minutes separately?
Swap says:

I have to convert value excel calculation is 100 qty is one hour this value i have convert in time format
RAJESH LAUL says:

THANKS AND REGARDS FOR SUPPORT
Gagneet says:

Would it be converting 2:53 to Hours then multiplying by 30 degrees?
2:53*24=2.8833 then 2.8833*30=86.50 Degrees
Gagneet says:

Need help converting hh:m to Degrees on a circle. For example 2:53 to degrees.
Sam says:

25days * 10:30 hour = ? In hh:mm format ?
Whts the formula like this calculation
Sam says:

27days * 10:30hours = ans in hours
How to formula for this type of sums ?
AJen says:

Thanks for the tutorial and for answering questions. I'm trying to convert a cell that is 'x days, y hours, z minutes, and t seconds' into minutes. I've tried a few of the recommended solutions however none have worked. What might I be missing or have wrong (i.e critical spacing I'm adding/missing or a specific format I need to have in the answer cell)? Also note, sometimes days, hours, , minutes, and/or seconds might have a 0 value. Often when I type them in the first referenced cell is highlighted like normal but every referenced cell following is not highlighted. Thanks in advance for your help. Here are the suggested formulas I've tried:
=INT(LEFT(C8 ;FIND("days"; C8) - 1)) * 24 * 60 + INT(MID(C8 ; FIND(" "; C8) + 1; FIND("hours"; RIGHT(C8 ; LEN(C8) - FIND(" "; C8))) - 1)) * 60 + INT(MID(C8 ;FIND(" "; C8;FIND("hours"; C8))+1;FIND("minutes"; C8)-FIND(" "; C8 ;FIND("hours"; C8)) - 1))

And I've tried this one:
=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))))
Nor Hashim says:

Thank you, Svetlana! For weeks, I have been looking all over for help that covers all my need for time/date conversion. Your answers work perfectly with my data!
Srikanth says:

How to convert number 102 to HH:MM
Ville says:

Hello,
Is there a format type or other way to quickly type hours to cell?
For example I want to add time 21:00 by typing only "21".
I have tried multiple different formats and if I type "21" it turns out 0:00.
Any idea about the format I should use?
- Davor says:
  
  Ville,
  Format cell as Custom, adding in "Type:" 00":"00
  So for eg. 7:15 you type 715
  
  Hope this helps,
  Davor
RAKESH says:

HELLO DEAR,
CAN I CONVERT NUMBER FORMET FOR EXAMPLE 1.75 IN TO TIME FORMET FOR EXAMPLE 1.45 HOURS IN MS EXCEL.
9015223435
- Navish says:
  
  I UNDERSTAND THAT YOU WOULD LIKE TO CONVERT NUMBER FORMAT (FOR EXAMPLE 1.75 IN TO TIME FORMAT WHICH IS 1.45 HOURS) IN MS EXCEL.
  
  > FIRSTLY, CONSIDER 1.75(WHICH IS IN GENERAL FORMAT) IN CELL A1. USE THE FORMULA IN CELL A2 AS =A1/24(THIS IS LIKE YOU ARE CONVERTING IT INTO HOURS). YOU WILL GET THE VALUE 0.072916667(WHICH IS GENERAL FORMAT).
  FINALLY, SELECT THE CELL A2 AND CONVERT INTO TIME FORMAT('HOME' TAB > GENERAL TO 'TIME'), YOU WILL GET THE VALUE 01:45:00.
  - Emily says:
    
    Navish, is there a way to do it the other way? If I have a time (let's say 8:30), how do I convert it to 8.5 in number format in excel?
    
    Thank you!
    - LaRae says:
      
      I would like to know the same thing
      - Hi,
        This question has been asked many times in the comments.
        To convert time to decimal, multiply by 24
        
        =A1*24
    - LaRae says:
      
      Figured it out. Have four columns: A start time column, an end time column, a difference in time (hh:mm) column, and a decimal time column (hours). Format the first three as time and the last as general. For the start time and end time columns, make sure it is in military time and you have the date entered in the cell as well (it should only show the time when not clicked on). In the time difference column, do a function (use =) and subtract your end time from your start time. This should appear in "hh:mm" format. In the final column, use this equation: =CONVERT( cell ,"day", "hr"). Replace the 'cell' part with your desired time difference cell. It should look like "=CONVERT([@[Hours (time)]],"day", "hr")" in the formula bar when you are done, with a box highlighting your desired time difference cell. This should convert your time in hh:mm to hours as a decimal. I used this to add my difference in hours into a total numeric value.
      
      For example, lets say I volunteer from 1 pm to 2:30 pm every saturday for a month (4 weeks). that is 13:00 and 14:30 in my first and second columns (respectively) for the first four rows. In all of my third columns (time difference) for the first four rows, it should read "1:30". In my final column, it would read "1.5". I can then add my final column together to get a total of 6.
      - LaRae says:
        
        You may be able to get it into three columns if you do a compound function to combine the third (time difference) and fourth (decimal time in hours).
Pavan says:

Dear
Our worker overtime in number(6.83) we want change in hour pls solve with example
- RJ says:
  
  6.83 multiplied by 24 then custom format and select hh:mm
  - RJ says:
    
    My fault should be divided by 24 then custom format and select hh:mm
Shaun says:

This is a small sample of my data that I need to convert to [h]:mm.
I can remove the words days, hrs and mins, although it's sort of a manual process by replacing them with nothing.
I will be adding it all up and providing some statistical info on the changes, as there are two columns with similar data. One for before and another for after. Sometimes the second column has no value, contains the word "Removed" or is Zero "0 mins".
1 days 7 hrs 49 mins
6 hrs 10 mins
14 hrs 25 mins
8 hrs 50 mins
18 hrs 4 mins
17 hrs 58 mins
1 hrs 21 mins
4 days 2 hrs 34 mins
2 days 20 mins
I need help. I found something similar, but not quite the same. Also it's been written in a function. I know I have to call a function, but am not totally sure how this would be done.
Thanks for any assistance or if someone can write a macro or VBA code for me?
Much appreciated.
Shaun says:

I have time format in 1 days 3 hours 24 minutes.
I need to change this to actual hours and minutes.

Any help much appreciated.
Thanks
Alex says:

Hi,
Is there a way to convert from 1 to 6 digits in a column to time HH:MM:SS
110759 11:07:59
110739 11:07:39
110723 11:07:23
41727 04:17:27
41714 04:17:14
124 00:01:24
106 00:01:06
48 00:00:48
24 00:00:24
I use the Text to column, fixed width function but , when they are not the same number of digits in a row they do not split properly.
Tried the Custom format by adding 0 in front of the digits and it shows 6 digits in each cell but when I try to use the Text to column it ignores the format.
Any ideas?
- Bob says:
  
  this is exactly what i'm searching for help on too. would love to see if anyone knows of a way to do this.
- Sridhara B says:
  
  Try with Delimited & space
Brock says:

I am trying to calculate lap splits for track athletes. I would like to calculate this in minutes, seconds and hundredths of a second. If I have elapsed time entered into a spreadsheet. Is there a formula that would calculate lap splits?
Kevin says:

Hello,
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. For instance, on Monday, there are 24 items that take 7 minutes each, for a total of 168 minutes. I need a formula that shows how many hours and minutes it will take. I can do the math myself and know that it takes 2 hours and 48 minutes (2 hours=120 minutes, plus 48 minutes). How do I get this to work in Excel?
Jack says:

Hi,
Please help. I want to convert the following duration to a decimal Date/Time value:
Input: 0 13:15:20
Input format: d hh:nn:ss
D=days
h=hours
n=minutes
s=seconds
Output required: eg: 0.5523
A decimal value of the duration.
Thank you for any help.
onyeka says:

25mins,1 hr 8 mins convert to time
onyeka says:

how can i convert an excel data of this format 25 mins to time data
Vivek Wadhawan says:

Hi Svetlana,

I have a question,

How can I get the difference between "1/29/2019 9:48:13 AM" and "1/27/2019 12:06:36 PM" in "HH:MM:SS" format, what formula I should use for such cases ?

Thanks is Advance

Vivek Wadhawan
- Bob says:
  
  Same question here. How do you convert DD:HH:MM:SS into HH:MM:SS ?
- Hi Guys,
  
  Sorry, I have overlooked this question somehow. To display the difference between the two dates in hours, minutes and seconds, use this format: [h]:mm:ss
  
  The hour unit code enclosed in square brackets is used to display more than 24 hours. For more information, please see How to show over 24 hours, 60 minutes and 60 seconds in Excel.
  - Terry Westra says:
    
    I have DD:HH:MM:SS and need to convert to decimal value. You're saying I have to first change to [h]:mm:ss or hh:mm:ss (somehow) and then convert? I don't see any direct way to go from dd:hh:mm:ss to decimal value in any of the formulas. hh:mm:ss to decimal is very straightforward, but adding days to it doesn't seem to work.
- Nguyễn Tiến Hải says:
  
  number in seconds: 1.201
  function: TIME(0;0;1.201)
  result: 0:20:01
  
  number in seconds: 430
  function: TIME(0;0;430)
  result: 0:07:10