Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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. #1
    New Member
    Join Date
    Jul 2006
    Posts
    4

    Default 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. #2
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default

    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
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  3. #3
    Board Regular
    Join Date
    Jun 2006
    Location
    Back in Luxembourg
    Posts
    779

    Default

    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.
    Riaz
    ------
    You may spreadsheet in columns, you may spreadsheet in rows
    But the more you spreadsheet, the faster it grows.

    ------
    Using Excel 2007 at work (done testing), 2003 at home

  4. #4
    New Member
    Join Date
    Jul 2006
    Posts
    4

    Default

    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. #5
    Board Regular
    Join Date
    Jun 2006
    Location
    Back in Luxembourg
    Posts
    779

    Default

    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
    Riaz
    ------
    You may spreadsheet in columns, you may spreadsheet in rows
    But the more you spreadsheet, the faster it grows.

    ------
    Using Excel 2007 at work (done testing), 2003 at home

  6. #6
    New Member
    Join Date
    Jul 2006
    Posts
    4

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Jun 2006
    Location
    Back in Luxembourg
    Posts
    779

    Default

    ROUND(($E65-INT($E65))*24,2),0)*35 should do it.
    Riaz
    ------
    You may spreadsheet in columns, you may spreadsheet in rows
    But the more you spreadsheet, the faster it grows.

    ------
    Using Excel 2007 at work (done testing), 2003 at home

  8. #8
    Board Regular phxsportz's Avatar
    Join Date
    Jun 2006
    Location
    Phoenix, AZ
    Posts
    1,985

    Default

    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
    I used to have a Darkroom, Now I have a PC... And it smells better
    WWW.SPUDSBAR.COM

  9. #9
    Board Regular phxsportz's Avatar
    Join Date
    Jun 2006
    Location
    Phoenix, AZ
    Posts
    1,985

    Default

    PS, My solution is only set to deal with billable hours of 23:59:59 or less...
    I used to have a Darkroom, Now I have a PC... And it smells better
    WWW.SPUDSBAR.COM

  10. #10
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,199

    Default

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com