PDA

View Full Version : Help with an excel formula?



JWilliams.RadiantDetail
01-22-2020, 11:03 PM
I'm AWFUL at Excel, I think maybe worse than I am at getting car windows streak free :(

Here's my dilemma, I have cells that have numbers, the numbers are 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. I *thought* I could do this.
so in another cell I put 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 made sense to me, well the /60 did because 60 = an hour. Only issue there is I looked and one of the results was 5.83 for hours. Which is not how I want it to show, I'd like hours and minutes. So, I Googled this, and then I Googled some more, then a little bit more. I have a slight grasp, 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.

I put 1:30 in the cell thinking [m] would show 90, and it did. but in the formula it shows 1:30:00 AM, which isn't what I should be seeing there.

I don't need the date or the time, what 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. example:

hand wash - 45
spray wax - 20
vaccuming - 25

and Excel knows the numbers are minutes and doesn't try to bombard me with a date or hours like 12:00AM

I HATE EXCEL lol.

JWilliams.RadiantDetail
01-23-2020, 12:26 AM
So I googled this more and Excel can't process time duration only time like 1:30AM, which is weird but would explain my problem. So, now I think all I need to do is change the cells with 30 or 90 or whatever in them to general numbers and re-work the formula I put in the 1st post. But, all the Googling in the world probably won't help here as I'm illiterate to anything that's not basic math lol.

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. 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 suppose to add them up and divide 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...

mwoolfso
01-23-2020, 11:31 AM
I sent you a PM with my e-mail address. Send the XLS over and I'll have a look. Simply add one or more columns to the spread showing exactly how you would like the end result to be on a few lines and I'll adjust the XLS for you.

JWilliams.RadiantDetail
01-23-2020, 06:04 PM
Excel SUCKS when you never made it past Pre Algebra in school, I really appreciate you reaching out. Someone on another forum help med, but yes I will be back to ask for your assistance before I'm done I'm sure of it. I'll probably never understand Excel formulas well enough to do more than either get lucky or do some real basic stuff. Thankfully, once I'm done with it I won't have to mess with it any more.