# Formula for billable time

This is a discussion on Formula for billable time within the Excel Questions forums, part of the Question Forums category; Hi Everyone, I am trying to figure out how to do an invoice for billable hours and minutes. I have ...

1. ## Formula for billable time

Hi Everyone,

I am trying to figure out how to do an invoice for billable hours and minutes. I have one column for total hours (example 1.03 - one hour & three minutes) and the hourly rate ( \$35.00). I can't figure out the formula for the total. It keeps coming out wrong.

Any suggestions?

Thanks!

Jen

2. Quick Note,
If you are using 1.03 for 1 hour and 3 minutes, I would suggest you seperate the hour and minute into different fields.

Are you sure it's not a decimal representation of 60 minutes?
1.03 = 1 and 1.8 minutes?

If it is hours and minutes this should work. Assuming your rate is in Cell A1 and the timestamp is in B1.
=LEFT(B1,FIND(".",B1)-1)*A1+((RIGHT(B1,LEN(B1)-FIND(".",B1)-1))/60)*A1

HTH
Cal

3. I use the following to turn my time into billable hours (change E65 to suit yourself).

ROUND((\$E65-INT(\$E65))*24,1),0)

The value in e65 is formatted for time, so your 1 hour 3 mins would be entered as 1:03. This will evaluate as 1.1 hours billable time. We bill in increments of 6 minutes, so 12 mins is 0.2 hours and so on.

If you are billing actual time, change the ,1 to ,2 to round to 2 decimals. Your 3 minutes would then be billed as 1.05 hours.

Hope this helps.

4. You guys are the best!

I must be Excel challenged because you lost me

Here is what I have

Hours Rate Total
B18 C18 D18

1:03 \$35.00 ?

What formula do I enter for D18? Could you just clarify for me?

5. It appears that you are not billing the three minutes, otherwise your bill would be \$36.75.

If so, enter in D18

ROUND((\$E65-INT(\$E65))*24,0),0)*35

Let me know if you need further assistance.

Kind regards

6. Hi Riaz,

Actually I do want to bill the three minutes. The \$35.00 is the hourly rate. So I need the formula to enter into D18 that would make it total \$36.75.

7. ROUND((\$E65-INT(\$E65))*24,2),0)*35 should do it.

8. If your hours are in B18 then this will calculate your fee's. However, you need to enter it as TIME and not Decimal.

IE 1 Hour and 3 Minutes = cell entry of 1:03 NOT 1.03 as this would be 3 10th's of an hour and not 3 minutes..

But if you utilize 1:03 then this will do the trick for you with no rounding. Just place this in D18 to get your total
Code:
=((B18-"12:00:00 AM") * 24)*C18
Assuming that
B18 is the number of hours
C18 = Rate
D18 = Rate * Hours

Hope it helps

9. PS, My solution is only set to deal with billable hours of 23:59:59 or less...

10. Hi Riaz,

Actually I do want to bill the three minutes. The \$35.00 is the hourly rate. So I need the formula to enter into D18 that would make it total \$36.75.

Try,

=(A1*24)*B1

Where A1 houes 1:03 and B1 \$35.00

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•