# Thread: Calculating costs based on times (XL 2003)

1. 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.

2. 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]

3. Thank you so much for your help. Knew I must be missing something obvious!

4. 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
•