Question Help with Excel :(

[QueBert]

Here's my dilemma, I have cells that have numbers, the numbers are suppose to be minutes. But, I just had the cell set as a general number because the formulas under the time option weren't anything close to what I wanted. And I *thought* I my method would work okay. In another cell I have this formula

=SUMIFS($D$2:$D$48,I2:I48,"=X")/60

I can't even take credit for this, I got help - here I think. It add's up the cells then divides by 60 to give me my hours seems simple enough. Only issue is I looked at one of the cells it's used in and the result was 5.83 for hours. I'd like it to be displayed in hours and minutes. Because it doesn't know it's suppose to be diving hours, makes sense but I don't have a clue what changes to make. So, I Googled this, and then I Googled some more, then a little bit more. I have a slight grasp, and to the best of my brain power I came up with making a custom number with [m] as the type. That's minutes so it should work. So, I put 3 in the cell for the value and I get 4320. Hummm so I look at the formula and see 1/3/1900 12:00:00 AM. Thinking I need to put time like X:XX I changed the cell to 0:03, and thought I had success until I looked at the formula box and saw 12:03:00 AM


I don't the time of day, all I'm trying to do is to put how long it takes me to complete a task in minutes. Obviously I need a custom number formula, but all the Google in the world isn't making me any smarter here. I'm not searching for the right thing apparently. I just want Excel knows the numbers are minutes, and doesn't make minutes into 12:30AM (or whatever) Maybe it's just easier to change the formula I put here and just have the number as general? If that's the case I still have zero clue what to change the formula to. I would like it to display in hours not minutes though. If this is confusing, here's an example:

Cell A1 45
Cell A2 25
Cell A3 20

and in my =SUMIFS($D$2:$D$48,A1:A3,"=X")/60 formula it will know the values in the D2=D48 are minutes and would show 1.5 for the total hours.



*UPDATE*
As I Googled more, apparently Excel can't process time duration, only time of day. I tried leaving the number field to [m] and putting 0:30 in all the boxes to test it, and when I look at the cell that add's them up and divides by /60 it's showing .17 for all the cells with the formula, even though they all had different values before I changed the number from general to [m] I'm lost...
I HATE EXCEL lol.

 

[Atari2600]

For the last bit, don't divide by 60

=numbervalue(00:30)

will return 0.020833, which is the fraction of a day that half an hour is, then multiply that by 24 to get back up to 0.5 - which is obviously a half hour.


For the first bit, if you want to turn 5.83 into hours and minutes
Say your 5.83 is in cell D12
=ROUNDDOWN(D12,0)&"H"&ROUNDDOWN((D12-ROUNDDOWN(D12,0))*60,0)&"M"

If you wanted hours, minutes and seconds
=ROUNDDOWN(D12,0)&"hr "&ROUNDDOWN((D12-ROUNDDOWN(D12,0))*60,0)&"min "&ROUND((((D12-ROUNDDOWN(D12,0))*60)-ROUNDDOWN((D12-ROUNDDOWN(D12,0))*60,0))*60,1)&"sec"

 

[QueBert]

THAAAANK YOU! I actually understood that enough to make sense of it. I would not have gotten there on my own, not anytime in in 2020 anywho. I need to go buy a book and read up on Excel.

 

[Atari2600]

THAAAANK YOU! I actually understood that enough to make sense of it. I would not have gotten there on my own, not anytime in in 2020 anywho. I need to go buy a book and read up on Excel.

No no - absolutely don't do that - its a total waste of time.

When you hit a problem, you need to figure out the kind of keywords to put into google.

So for yours, it'd be something like "converting time to decimal in excel"

Places like stackoverflow or www.cpearson.com (Chip Pearson) are excellent source of information - and they have search engines which are far better than any book.