# Numbers formula help

Discussion in 'Mac Apps and Mac App Store' started by hedrivesmexico, Mar 14, 2009.

1. ### hedrivesmexico macrumors newbie

Joined:
Mar 14, 2009
#1
I'm trying to create a template for legal billing. The only thing I'm really having a problem with is the formula that multiplies time by hourly rate. The time spent cells are formatted to show it as 4h 5m ... and that totals perfectly with the SUM formula. When I multiply it with a rate cell formatted to currency the math is fine, but the format stays as 00h 00m ... even when I format that cell to currency.

What am I doing wrong?

Thanks!!

2. ### reclusivemonkey macrumors 6502

Joined:
Jun 2, 2008
Location:
Sowerby Bridge, West Yorkshire, UK
#2
When you format numbers as time in a spreadsheet, it goes in as a time of the day, not a simple count of hours and minutes. Therefore the maximum you can have is 23 hours and 59 minutes. Any more than this and it just goes back to 0 hours and counts on from there. If you want to make calculations on time, then you will have to convert to decimals. I've not tried this in Numbers, but its certainly that way in Excel and I suspect from what you have said its the same in Numbers. If you get stuck let me know as I have a formula that will work this out.

3. ### hedrivesmexico thread starter macrumors newbie

Joined:
Mar 14, 2009
#3
Thanks for your reply. I never thought of the element you mentioned. However, upon testing that, Numbers seems to have that issue worked out. It will give me a total of hours without a 24 hour limit.

I tried to use the product formula to multiply the hours and minutes by the hourly rate (formatted for currency), but it still comes up at hours and minutes. I can't make the sum come up as currency ... even with the cell formatted that way. Is there something to put in the formula to make it choose the currency format? Or is there a formula I need to hide in another cell to do some sort of conversion?

4. ### Julien macrumors G4

Joined:
Jun 30, 2007
Location:
Atlanta
#4
I also have a similar question. I have a sheet that I keep up with the time I run. In Excel it would keep a running running (pun) total but I get an error message in Numbers. Here is a screen shot of the part I'm having trouble with.

5. ### reclusivemonkey macrumors 6502

Joined:
Jun 2, 2008
Location:
Sowerby Bridge, West Yorkshire, UK
#5
In that case, I think you will have to format the time into a decimal, apply your currency rate and then convert back into minutes. I don't have numbers so unfortunately I can't advise you on specific formulae, but basically it will be along the lines of using HOURS*60+MINUTES of your time.

6. ### reclusivemonkey macrumors 6502

Joined:
Jun 2, 2008
Location:
Sowerby Bridge, West Yorkshire, UK
#6