Thinking about changing how long I spend on a task into a percentage
Thinking about changing how long I spend on a task into a percentage
I have a list of numbers in an HH:mm format like 2:15, 3:00, or 1:45. To change 2:15 into a percent (which is about 2.25 hours), I used this formula. =hour(mod(F5,1)) + int(F5)*24 + minute(F5)/60 It works okay for times that aren't full hours like 1:0 or 2:0. But it fails when there are zeros in the time part, like 2:0 because the function gets confused. The error message says the MOD parameter needs numbers but F5 is text, so it can't be turned into a number. I need some help with this.
I'd make sure to work in minutes instead of hours, so I can just add them up. Like this: (Hour from cell F5 times sixty) plus the minute from cell F5. But what is that percentage talking about? If 100% means one hundred percent, how do we figure out which number goes there?
I think you are talking about Decimal time. There are a few ways to do it. My favorite for Excel is: =TEXT(A1*24,"0.00"). You can also wrap that in TIME or TIMEVALUE to turn it into parts of a day and use those kinds of things.
Oh yeah, you mean like hours and minutes instead of just percentages. Yeah, thanks! I'm going to try it out.
Thanks to both of you for your replies. Sizzling: your idea makes the following happen. F5 takes 30 minutes (half an hour), which equals .5. Eximo: your suggestion leads to this next example where it takes 45 minutes (forty-five) and results in .5. I'm not sure what's happening here. Is the :45 minutes being turned into a half-hour? The other issue is that when I add these numbers, I end up getting 45.5 hours instead of the right result.
You have to take apart the minutes and split them apart on their own. A thirty-minute mark gives you half, while a forty-five-minute mark gives you three-quarters.
I am getting the wrong error message when I enter hour values like 1.0 or 2.0. My whole hours don't work with everything except numbers, like 1:0 or 3:0. It gives me this exact response: #VALUE! The function MOD needs number inputs, but '2:0' is actually text and can't be turned into a number.
Do you put your cells in the right way? The Time part has to look like HH:MM, and the Decimal part needs to have exactly two numbers after the dot.