Formula for billable time

Todaysadmin

New Member
Joined
Jul 18, 2006
Messages
4
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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.

Add your charge out rate as necessary.

Hope this helps.
 
Upvote 0
You guys are the best!

I must be Excel challenged because you lost me :confused:

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? :LOL:
 
Upvote 0
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

You will get $35 as your answer.

Let me know if you need further assistance.

Kind regards
 
Upvote 0
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.

Thanks for your help.
 
Upvote 0
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
 
Upvote 0
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.

Thanks for your help.

Try,

=(A1*24)*B1

Where A1 houes 1:03 and B1 $35.00
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top