Excel: Covert decimal hours Excel's hours & minutes (8.25 to 8:15)

I'm frequently asked how to convert decimal hours to Excel's hour and minute format. A couple of examples: 7.75 need to show as 7:45, and 30.25 needs to show as 30:15. The solution is to divide by 24 hours. The formula is hours/24. When you divide 7.75 by 24, you get 0.322917. After getting the answer, you format the time with an hour and minute format using Format Cells in Excel. The format is h:mm This works since one day (1) is 24 hours. 12 hours divided by 24 is 0.50.

Great screenshot from Excel
Column A contains decimal hours. Column B is the hours in A divided by 24. Column C is the same answers in column B but using the format h:mm

Chris Menard's blog post

Chapters:
0:00 Intro
0:30 Convert to Hours
2:38 Covert to minutes

What about duration over 24 hours?
To display hours over 24 hours, recall I mentioned 30.25? Just adjust the number format. Just wrap the hours, which is the letter h in square brackets [h]:mm

Excel video - Working with Time in Excel
Download the Excel file used in this video

How do I convert to minutes?
To convert to minutes, divide by 1440 instead of 24.

#msexcel #excel #chrismenardtraining

Chris Menard's Website

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[danzer]

The hint I needed: divide the decimal total of minutes for 1440. I was dividing by 60 and getting crazy. Tks!!

[jamesmyrthong]

Thank You for this straight to the point tutorial. Very helpful indeed. I just tried a few minutes ago.

[akinsamuel]

Hi Chris, I hope you can help. Is there a simple formula an formatting that will add a column of hours together and total them at the end?
For example:

3:00
3:45
3:00
2:45
3:00
4:15
2:45
2:30
0:00
0:00


24:30 - <--- 24 hours and 30 mins altogether.


When I try this I keep getting the time formatting that goes back to zero. Your currency trick works well because it totals each cell and gives me a currency amount at the end. But so far I have not seen a solution to this problem that works over 24 hours without going back to zero. Glad of your help. Thanks

[rocketcitymoto]

Thanks so much! It is so easy to do but so hard to figure out, until I watched this video! Thanks again :)

[ExplorerLink]

I'm trying to do the reverse. I have 8:15 and I need to convert it to 8.25 (if that is the right math LOL)

[JRayGlover]

Thank you!! This was just what I needed!

[judithelliott]

Thank you! With your help I managed to fix a problem I just couldn't on my own!

[adeenrehan]

Sir,
For example if the cell value shown is 4.15 then how we will shown the value as 4:15 in another cell to displays as time. Is there a formula to make this list of decimal time as actual time with the colon or do we need to some sort of formatting?

[krzysztofjastrzebski]

Thank you Sir for excellent tut.
I have a task that's seems to be easy but I cannot figure out how to do it. I need to calculate, divide litres by hours for example 2 Litres/4 hours. Appreciate any help.

[piratedunknown]

Actually I have recorded time of entry and exit in Decimals; like if I started at 9:30 I have entered 9.3 and exit time is 6:55PM I have entered 18.55... How to convert this into time? Kindly help

[BernardNieto]

This is great! I had a follow up question - I have a column for example - 6 days, 1 hour - what would be the quickest way to convert this to hours??

[dougferguson]

Thanks for the video. I want to be able to add time durations. Let's say I know the durations of 5 videos and want to add them up to give me mm:ss and if the total is more than an hour show hh:mm:ss? I'm not sure exactly how to format the input cells as well as the output cell.

[ryandriver]

Hi Chris, thanks for the video, this helped clear somethings up for me, but I have a follow-up...

I am listing times in a column (times to complete an escape room actually) 45.15, 51.50, 30.55 ...
I would like to work out the AVERAGE time it takes to escape all my rooms.
When I try the formula you have provided, it doesn't work. I assume because it doesnt recognise the seconds as being seconds in the Time column.

How can I fix this? Your example doesn't identify seconds with the minutes, only full minutes, so I guess what I am trying to do is more complicated!? HELP! Thanks!!

[SegiLogistics]

I have a set of data which were compile in 24 hours format but the data instead of using the colon(:) it uses dot(.). For example 13:30 is recorded as 13.30, is there a way to rectify this?

[agamenonschepelski]

Ola,

Sou do Brasil, e agradeço muito pelo seu vídeo, o único que achei mostrando a conversão de decimais para horas, ou seja, só pegar o valor DECIMAL e DIVIDIR por 24 e a Magica acontece.... se caso não der certo é só ir em FORMATAR CELULA e selecionar HORA e depois escolher a opção 37:30:55.. E tudo resolvido.

THANK YOU VERY MUTCH

[normsabbey]

You just made my job easier! Thank you Sir, for your expert knowledge and for sharing it with us. I'll be subscribing!! :)

[LolaCopacabanita]

I need help :(

I have 2:15 hours that I need to subtract from 224 hours. When I do it only shows 21:45 in the format hh:45.. but how do I do to show 221:45 which is the exact hours substracted in the excel. It shows the wrong format as only 2 digits appear out of the 3 hours prior to the colon. HELP! -In my case, It's hours (2 hours and 15 minutes; substracted from 224 hours).

[irishdancercm]

I am calculating time to decimal for items that are related to response times. New to Viewed, Viewed to Action, Action to Close. I can't find any solutions for this.

[andreagoggins]

Hello, How can I make the data be in two separate columns? Like if the current data is 99 minutes and I want 1 hour in column one and 39 minutes in column 2?

[Cheeksbuster]

Great explanation, could you also explain to me, why Excel needs the numbers to be divided by 24?