Hello! I apologize in advance, but I really am new to formulas... here's my problem: I am a truck driver who is creating a spreadsheet that keeps track of Hours of service, based off of 70 total hours, which I currently have displaying as a decimal (70.0). The problem I am having is getting the minutes to display as the hours and actual "clock" minutes instead of out of 100 minutes. For example, right now I have 26 hours and 58 minutes left, but it is displaying 26.98, not as 26.58, which is what I want. I thought maybe an IF statement would work, or something? Any easy formulas?
JJubjab
Well-known Member
JoinedJan 3, 2007Messages995
- Oct 25, 2013
- #2
If you format it as time it should work just like that.
Try entering 70:00 in one cell, 43:02 in the next, then calculate the diff between those two; you should end up with 26:58.
Jjgrammes
New Member
JoinedOct 25, 2013Messages4
- Oct 25, 2013
- #3
Let me clarify further; when I format for "time", it gives me the time. I want it to only display the amount of time, not the time. If the amount of time say is 69 hours and 58 minutes, I want it to be displayed as "69.58". Instead, it's showing me "69.98". What function or conditional formatting can I use so that anything over 59 minutes will increase the # to the left of the decimal to the next higher number, and everything to the right of the decimal will not decrease below 0 or increase over 59?
JJubjab
Well-known Member
JoinedJan 3, 2007Messages995
- Oct 28, 2013
- #4
If it is showing 69.98, it is NOT formatted as time. You need to format that cell as time as well.
Mmfexcel
Well-known Member
JoinedJan 8, 2009Messages791
- Oct 28, 2013
- #5
[h]:mm:ss is the format you need (under custom format)
but still, it will show 69:58 instead of 69.58 you ask for.
Jjgrammes
New Member
JoinedOct 25, 2013Messages4
- Nov 2, 2013
- #6
That's ok to have the ":", but when I change the format to [h]:mm:ss, it gives me "1679:34:17"??
mikerickson
MrExcel MVPJoinedJan 15, 2007Messages24,318
- Nov 2, 2013
- #7
If you have decimal hours in A1, =TEXT(A1/24,"hh:mm") will display the time in standard hours:minutes format.
Better yet, put =A1/24 in a cell and format the cell for time. Then these arithmetic can be performed on these helper cells if needed.
Jjgrammes
New Member
JoinedOct 25, 2013Messages4
- Nov 2, 2013
- #8
GOT IT!!! THANKS SO MUCH! The final formula I used was this: 1st, I formatted the cell [h]:mm, then the cell actually had an equation in it, so my formula I wrote like this: =(H3-G11)/24, and that did the trick! Thanks!
Final Formula: =B3/60
Converting Minutes to Hours
In order to convert minutes to hours, we simply divide the number of minutes by 60 because there are 60 minutes in every hour.
- We enter minutes in cell A3
- In cell B3, enter this formula =A3/60
We can see in this Example, the formula in B3 returns “4” because 240 divided by 60 is equal to 4. Thus, 240 minutes is equal to 4 hours.
Instant Connection to an Expert through our Excelchat Service:
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.