+ Reply to Thread
Results 1 to 14 of 14

calculating time

  1. 07-04-2013, 02:21 PM #1
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    calculating time

    I charge clients a flat rate per month and if they exceed per month 30 hours I have to prorate them and re-charge for this extra time. The list that I have attached has the monthly hours that i have maintained for a client and I don't know how to sum in excel the total hours. how can i do this?
    Reply With QuoteRegister To Reply
  2. 07-04-2013, 02:25 PM #2
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    here is the upload
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 07-04-2013, 02:28 PM #3
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: calculating time

    Edit..

    Uploaded file found..

    Check this one..

    excelforum tally hours.xlsx

    PS: Use cell Format as [HH]:MM:SS for calculation > 24
    Last edited by Debraj Roy; 07-04-2013 at 02:36 PM.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...
    Reply With QuoteRegister To Reply
  4. 07-04-2013, 02:48 PM #4
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    Thanks Roy!
    the total # of hours that you got was 137 hours so this client has exceeded the 30 hours for month.

    ??
    =SUM(F9:F31)-TIME(30,0,0)
    Reply With QuoteRegister To Reply
  5. 07-04-2013, 02:49 PM #5
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    to this file i want to add the weekdays. what formula can i use to obtain the weekdays?
    Reply With QuoteRegister To Reply
  6. 07-04-2013, 03:03 PM #6
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: calculating time

    Hi djmatok..

    Please check the updated file..

    excelforum tally hours.xlsx
    It demostate.. to check if a day is weekday or not...

    Formula used..


    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    &
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    You can use any of the above for your next step..
    Reply With QuoteRegister To Reply
  7. 07-04-2013, 03:07 PM #7
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    actually I want the formula to say "monday", "tuesday" , etc...
    Reply With QuoteRegister To Reply
  8. 07-04-2013, 03:09 PM #8
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    got it. I will use the 2nd formula provided. sorry for that
    Reply With QuoteRegister To Reply
  9. 07-04-2013, 03:11 PM #9
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    I want to show at the top :
    actual hours - fixed hours = excess hours

    excess hours * rate = billable hours/USD
    Reply With QuoteRegister To Reply
  10. 07-04-2013, 03:26 PM #10
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    how can I show the excess number of hours correctly
    Reply With QuoteRegister To Reply
  11. 07-04-2013, 03:48 PM #11
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: calculating time

    Hi ..

    try this..

    to show the # of hour.. use this..

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    and then apply rate accordingly..
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    https://dl.dropboxusercontent.com/u/...y%20hours.xlsx
    Reply With QuoteRegister To Reply
  12. 07-04-2013, 04:09 PM #12
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: calculating time

    Does this come close to what you want?
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  13. 07-04-2013, 04:24 PM #13
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: calculating time

    Revised showing weekends
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 07-09-2013, 11:36 AM #14
    djmatok
    djmatok is offline
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: calculating time

    thank you guys
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1