Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jul 2006
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to set up a spreadsheet to calculate the amount of time spent on individual jobs and to apply charges to each one based on an hourly rate. I'm attaching a sample workbook with simplified data.
    Some of the jobs take place overnight, so I've written a formula in column D that seems to calculate correctly. However, I now need to calculate the cost for each job in column E at an hourly rate (cell C1). For Job 1, I was expecting to see 1,300 (13 hours x 100) but don't seem to be able to make this work! Can anybody help me out here - or am I completely on the wrong track?? With many thanks in advance.
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You need to multiply the duration in hours by 24.
    Excel stores TIMES as fractions of a day, so 1Hr = 1/24 as a decimal

    Hence your formula needs to be

    =(D4*24)*$C$1

    Also you need the Number format of Column D to be [h]:mm
    [Edited By AKW] Of Course when I said C I meant D I just wasn't looking at it.

    This way IF it was to roll over 24 hours it would show the hours correctly,
    although that has nothing to do with the calculation

    Have attached Example back as well.

    [attachment=88676:Example.xls]
    Attached Files Attached Files
    Andrew

  3. #3
    Lounger
    Join Date
    Jul 2006
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your help. Knew I must be missing something obvious!

  4. #4
    New Lounger
    Join Date
    Apr 2010
    Location
    Michigan
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your formula is working fine - it's the cell formatting that is messing you up... You have formatted the cells in Column D to show you how many hours are in the time period and that is what Excel is showing you, but behind the scenes Excel has actually calculated a percentage of a day, so the true result of the calculation is something like 0.54166 of a day. The formula to calculate the total cost needs to take that into account. I used =(D5*$C$1) * 24 to end up with the correct total cost, but there are probably several ways you could deal with the problem.

    Hope this helps!

Posting Permissions

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