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 -
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!!
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.
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
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?
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.
I would use Flash Fill. Here is a screen shot You need the days in one column and the hours in another. Finally, add them together (column D) in my screenshot. drive.google.com/file/d/1JozVYiJpIoielonpu22DBt3elBwFEiCb/view?usp=drivesdk
@@ChrisMenardTraining thank you so much! Sorry, another follow up to the above. I have 2 columns in this format Tue, 2nd Nov 2021 10:56am. What would be the best way to calculate the time in difference between them? ie. Tue, 2nd Nov 2021 10:56am to Thu, 4th Nov 2021 3:52pm. Sorry to hijack the thread!
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
I like to have this format but instead of the colon can you put hrs and mins after the whole number? I have a truck Manifest that I break down in different departments and then by hours to work for freight. So, example in G5 there is 5 cases of freight, there suppose to work 16 cases/hr, in I5 the formula is =round(G5/16,2) which gets me 0.31, now I want that to equals to 18.6 or 19mins, that's my end goal, anyway to help me?
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.
Hi Chris, first time view, long time excel user. I think one of your other video covered this. Question our DBase puts out time as a decimal of 100, ie for 1:45min would be 1.75.....or 1 hr and 75% of 60min. Another example is 6.96 hrs, using your video I know it is 6:57min. From 6:57 I need to subtract out the first half of the day, easy easy, but......... The real question is How do I get 6.96 to show 6:57? will i have to create multiple columns, help? Is it the tabulation video you did?
Add a column to the right of the decimals. Now take the decimals and divide by 24. While selected, use the Time format you want. Here is a screenshot I did for you. drive.google.com/file/d/1CBOia-bzw2Ubs9-JEF94gvRteTR0qJbc/view?usp=drivesdk
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?
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?
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).
I created a time sheet to do payroll in excel. When I get my total hours and minutes worked in one cell, how can I get that total to automatically show me the minutes in decimal without changing the total hours. Example: my total time worked is 40:29, How can I get it to convert total hours worked to 40:48? Thanks in advance!
hi Chris, I've made an error by putting 6 mins & 25 secs in as 6.25 (using a decimal instead of a colon) I've done this for over 1000 cells - is there a way to convert it? Thanks in advance!
Hello Chris, Thanks for this video. I just have a doubt, the sort doesn't work on the time. Even If I extract the minute information from a time stamp and then format it to number format and do the sorting, it still doesn't work. I used minute (A1) to extract the minute info.
Hey Chris I have an excel sheet that contains two lists with 3 columns the first column being names, the second is pay period start (i.e. 3-28-2020 or 4-11-2020), the third is pay period end (i.e. 4-10-2020 or 4-24-2020). My task here is to compare the two lists and find the matches. Another issue I have within this is that one list has just last name, first name whereas the other list contains last, first, and middle name. Any chance you could help me out with a way to find matches and differences?
Back up your files. Make the names match. This is easy to do with Flash Fill. I wouldn't worry about the middle initial. Copy data from one file to another and now use either Conditional Formatting to find duplicates or use a PivotTable and count.
Hi - Great Video and what you are doing with the green screen is what I am working on learning for my presentations so thanks for the extra information. I am making a chart to convert running speed to running pace - i.e. - Speed is 4.0 mph - Pace is a 15-minute mile (15.0) - Speed is 5.5 mph - Pace is 10.91 minutes as a fraction of 60 seconds which would equal 10 minutes 54.6 (55) seconds. I want the chart to display 10.55 vs. 10.91. I tried doing what you did for hours and minutes with minutes and seconds but the conversion was all funky. For example 10.91 converted to 49.05.5 Any ideas suggestions on how to display a minute second decimal number to minutes and seconds . Thank you...
Two easy steps. 1) Divide the 132 by 1440. 2) Format cells and make h:mm Screenshot is here drive.google.com/file/d/1GnBbZ5S9-oZaPqVXgg4wzA7CdOW7Sx0M/view?usp=drivesdk
The hint I needed: divide the decimal total of minutes for 1440. I was dividing by 60 and getting crazy. Tks!!
You are welcome.
Thank you! With your help I managed to fix a problem I just couldn't on my own!
Glad I could help!
Accurate, concise & clear. 10/10
Thank you! Can't beat 10 out of 10!
Thank You for this straight to the point tutorial. Very helpful indeed. I just tried a few minutes ago.
Thank you.
Thanks so much! It is so easy to do but so hard to figure out, until I watched this video! Thanks again :)
Thank you!! This was just what I needed!
saved my life
happy to help
You just made my job easier! Thank you Sir, for your expert knowledge and for sharing it with us. I'll be subscribing!! :)
glad to help.
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 -
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!!
This helped. Thank you 😇
you are welcome.
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.
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
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)
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?
what about this solution. drive.google.com/file/d/1GTMqteGo2RfgKfemuu93qhQmTHuybVxf/view?usp=drivesdk
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.
ok got it... i was using a wrong formula... now it works...
I'm glad it is working. Thank you.
Really helpful, thank you!
You're welcome!
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??
I would use Flash Fill. Here is a screen shot You need the days in one column and the hours in another. Finally, add them together (column D) in my screenshot. drive.google.com/file/d/1JozVYiJpIoielonpu22DBt3elBwFEiCb/view?usp=drivesdk
@@ChrisMenardTraining thank you so much! Sorry, another follow up to the above. I have 2 columns in this format Tue, 2nd Nov 2021 10:56am. What would be the best way to calculate the time in difference between them? ie. Tue, 2nd Nov 2021 10:56am to Thu, 4th Nov 2021 3:52pm. Sorry to hijack the thread!
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
Thanks, this really helped
Thank you!
Great explanation, could you also explain to me, why Excel needs the numbers to be divided by 24?
24 hours in a day
I like to have this format but instead of the colon can you put hrs and mins after the whole number? I have a truck Manifest that I break down in different departments and then by hours to work for freight. So, example in G5 there is 5 cases of freight, there suppose to work 16 cases/hr, in I5 the formula is =round(G5/16,2) which gets me 0.31, now I want that to equals to 18.6 or 19mins, that's my end goal, anyway to help me?
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.
thank you ☺
Hi Chris, first time view, long time excel user. I think one of your other video covered this. Question our DBase puts out time as a decimal of 100, ie for 1:45min would be 1.75.....or 1 hr and 75% of 60min. Another example is 6.96 hrs, using your video I know it is 6:57min. From 6:57 I need to subtract out the first half of the day, easy easy, but.........
The real question is How do I get 6.96 to show 6:57? will i have to create multiple columns, help? Is it the tabulation video you did?
Add a column to the right of the decimals. Now take the decimals and divide by 24. While selected, use the Time format you want. Here is a screenshot I did for you. drive.google.com/file/d/1CBOia-bzw2Ubs9-JEF94gvRteTR0qJbc/view?usp=drivesdk
it worked, i was doing multiple columns when I really now only need one, thanks Chris, I will be back and I subscribed@@ChrisMenardTraining
I cam with a prblem, I got an answer. Thanks.
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?
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?
Thank you so much sir
Most welcome
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).
See if this works. Go to Format Cell and make sure hours and minutes are formatted like this * [h]:mm*
I created a time sheet to do payroll in excel. When I get my total hours and minutes worked in one cell, how can I get that total to automatically show me the minutes in decimal without changing the total hours. Example: my total time worked is 40:29, How can I get it to convert total hours worked to 40:48? Thanks in advance!
thanks
thanks a lot. it help me👍
Glad to hear that
hi Chris, I've made an error by putting 6 mins & 25 secs in as 6.25 (using a decimal instead of a colon) I've done this for over 1000 cells - is there a way to convert it? Thanks in advance!
yes, in the column next to it type =A2/24. Now, format that cell as Time.
14 hrs I want show is 12 hrs and 120 min how to set in excel
THANK YOU SOOO MUCH
You are welcome!
Thank you!
You're welcome!
Hello Chris, Thanks for this video. I just have a doubt, the sort doesn't work on the time. Even If I extract the minute information from a time stamp and then format it to number format and do the sorting, it still doesn't work. I used minute (A1) to extract the minute info.
I want to convert 5:43 to fractional time without doing the hour and minutes separately. It's that possible?
Yes. Multiple by 24 and then use Comma Style.
When I export time from the time keeping system, it reports the time as 7h 15m. How do I convert this to 7.25?
Hey Chris I have an excel sheet that contains two lists with 3 columns the first column being names, the second is pay period start (i.e. 3-28-2020 or 4-11-2020), the third is pay period end (i.e. 4-10-2020 or 4-24-2020). My task here is to compare the two lists and find the matches. Another issue I have within this is that one list has just last name, first name whereas the other list contains last, first, and middle name. Any chance you could help me out with a way to find matches and differences?
Back up your files. Make the names match. This is easy to do with Flash Fill. I wouldn't worry about the middle initial. Copy data from one file to another and now use either Conditional Formatting to find duplicates or use a PivotTable and count.
@@ChrisMenardTraining appreciate your help I managed to use a pivot table to get exactly what I was looking for thank you!
Hi - Great Video and what you are doing with the green screen is what I am working on learning for my presentations so thanks for the extra information.
I am making a chart to convert running speed to running pace - i.e. - Speed is 4.0 mph - Pace is a 15-minute mile (15.0) - Speed is 5.5 mph - Pace is 10.91 minutes as a fraction of 60 seconds which would equal 10 minutes 54.6 (55) seconds.
I want the chart to display 10.55 vs. 10.91.
I tried doing what you did for hours and minutes with minutes and seconds but the conversion was all funky.
For example 10.91 converted to 49.05.5
Any ideas suggestions on how to display a minute second decimal number to minutes and seconds .
Thank you...
Hello Chris, Great videos! The movie is 132 minutes. How do I set up Excel to show me 2 hrs and 12 min?
Two easy steps. 1) Divide the 132 by 1440. 2) Format cells and make h:mm Screenshot is here drive.google.com/file/d/1GnBbZ5S9-oZaPqVXgg4wzA7CdOW7Sx0M/view?usp=drivesdk
Thank you
You're welcome
loved it
Thank you
How to convert hours to Average time
Example: 136 hours to average hours in Time in hours
Does not work.
Thank you
You're welcome