Formula for billable time

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Formula for billable time

  1. #1
    New Member
    Join Date
    Jul 2006
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  3. #3
    Board Regular
    Join Date
    Jun 2006
    Location
    Back in Luxembourg
    Posts
    779
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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