Excel Excel calculate pay based on rate per minute

[hanswehr]

Hello all, hoping you can help me out with this as I've spent several hours on it so far to no avail!

I have a spreadsheet detailing the work my colleagues and I have done on a film. The durations of folders of footage we have each worked on are shown in minutes (column formatted [m]:ss) because our pay rate is per minute.

Now at the bottom of each column I have a 'total duration' row which shows the complete amount of footage (still in [m]:ss) that each person has worked on, and below that a row of rates per minute (formatted as currency), most of which are £0.90.

In the row below these two I need a formula for the total amount of money which each person should be paid. But I can't for the life of me figure out what it should be and I keep getting stupid answers like 0.1.

Any help very gratefully received....

 

[hanswehr]

Folks, I would be so grateful if someone could help me with this. I'm trying but getting nowhere. I tried converting the durations into seconds and then multiplying that by the rate but for some reason that doesn't work--when formatted as general it gives me almost the right answer, but rounded up to a whole number (which I don't want--I want it as currency to two decimal places) and when formatted as anything else it keeps giving me the stupid 0.1 answer!

 

[Becky]

Hello the hanswehr, welcome to PC Review!

Given the fact that your rates are determined per minute, I would have thought you would need to have the total time spend in minutes - ie as a whole number. You would then just multiply the number of minutes by the rate to get the amount earned. For example, minutes spent on various projects; 30 + 50 + 120 = 200 minutes (recorded as a whole number rather than 3 hrs 20 mins). 200 mins x £0.90p = £180

Would this approach work in your situation?

 

[hanswehr]

Hello Becky, thanks so much for your suggestion! Rounding it up does seem to work in terms of the formula, it's just that I have a lot of these different durations and I don't want to look like I'm trying to scam my employers by rounding it to a whole number every time! If there's no other way, I can do that, but it seems like there should be a more precise way of doing it...

Cheers
Kate

 

[hanswehr]

Also, if I do go for that option, what would the formula be for showing a duration rounded to the nearest minute? Let's say I have 45:37 (formatted [m]:ss) in A1, and in A2 I want to show 46 (formatted as number--when I tried to do it formatted as time it was still messing up for me even though it was a whole number). Is there a way to do that other than manually?

Thanks for all your help!

 

[Becky]

Hmmm I'm not sure, I'm a little rusty with excel to be honest! So the cells you are working from are formatted as time, is that right? Then would it work if you multiplied them by 60 x 24 to get the total minutes? eg. A1*60*24=A2

Just make sure that the 'total minutes' cell (ie A2) is formatted for numbers rather than time. Let me know how you get on!

 

[hanswehr]

Becky, what can I say, I really don't understand the logic but it worked. Thanks so much!

 

[Becky]

Brilliant! Glad to be of service