Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL GURU WANTED - Time calculations
Problem is when taking a time-start cell from a time-end and applying a rate
to the hours I get a different value from doing a simple calculation. ie simple calculation is 1.17 hours @ £130/hour = £152.10 Using times as Start Cell E2 = 13:00 & Cell D2 End 14:10 Calculating as: ((E2-INT(E2))*24)-((D2-INT(D2))*24) results as £1.17 Difference is 0.43? I have tried other calculations e.g. hour(d2) + minute(d2)/60) etc I am quite baffled can anyone help! If this 1.17 is used in the calculation the result is 151.67 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL GURU WANTED - Time calculations
Can't see why you're using INT in your formula.
14:10 - 13:00 * 24 is not 1.17. It's 1.16666666666667 1.16666666666667 * 130 = 151.666666666667 Try this: =ROUND((D2-E2)*24,2)*130 Result = 152.1 Format as GENERAL, NUMBER or CURRENCY -- Biff Microsoft Excel MVP "Robert" wrote in message ... Problem is when taking a time-start cell from a time-end and applying a rate to the hours I get a different value from doing a simple calculation. ie simple calculation is 1.17 hours @ £130/hour = £152.10 Using times as Start Cell E2 = 13:00 & Cell D2 End 14:10 Calculating as: ((E2-INT(E2))*24)-((D2-INT(D2))*24) results as £1.17 Difference is 0.43? I have tried other calculations e.g. hour(d2) + minute(d2)/60) etc I am quite baffled can anyone help! If this 1.17 is used in the calculation the result is 151.67 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL GURU WANTED - Time calculations
If you have actual time values in the cells, you can subtract them as normal
numbers, since they are in fact actual numbers. In Excel, times are stored as a fraction of a 24-hour day (6:00:00 = 0.25, 12:00:00 = 0.5, 18:00:00 = 0.75, etc). To find the elapsed time between times, just use =EndTime - StartTime If it is possible that the interval crosses midnight (e.g,., start at 10PM, end at 2AM the next day), use =EndTime - StartTime + (EndTime<StartTime) If EndTime is earlier than StartTime, the < comparison will return TRUE (equal to 1) and will add 24 hours (1 day = 1.0) to the result. In general, Excel cannot display negative times, but it will do proper arithmetic with negative times. Be sure to format the formulas above for time, either hh:mm or [hh]:mm. Using the square brackets [ ] around the hh tells Excel not to "roll over" at 24 hours. Thus, 30 hours is displayed as 30:00 rather than 6:00. For rate calculations, you need to multiply the time value by 24 to convert the serial time to a number of hours. For example, =(EndTime - StartTime + (EndTime<StartTime)) * 24 This formula should be formatted for currency, number or general, not Time. For a lot more information about working with dates and times in Excel, see www.cpearson.com/Excel/DateTime.htm -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Robert" wrote in message ... Problem is when taking a time-start cell from a time-end and applying a rate to the hours I get a different value from doing a simple calculation. ie simple calculation is 1.17 hours @ £130/hour = £152.10 Using times as Start Cell E2 = 13:00 & Cell D2 End 14:10 Calculating as: ((E2-INT(E2))*24)-((D2-INT(D2))*24) results as £1.17 Difference is 0.43? I have tried other calculations e.g. hour(d2) + minute(d2)/60) etc I am quite baffled can anyone help! If this 1.17 is used in the calculation the result is 151.67 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL GURU WANTED - Time calculations
Rounding error. Using this formula will give you the correct result:
=ROUND(HOUR(E2-D2)+MINUTE(E2-D2)/60,2) Hope that helps. On Dec 26, 4:16 pm, Robert wrote: Problem is when taking a time-start cell from a time-end and applying a rate to the hours I get a different value from doing a simple calculation. ie simple calculation is 1.17 hours @ £130/hour = £152.10 Using times as Start Cell E2 = 13:00 & Cell D2 End 14:10 Calculating as: ((E2-INT(E2))*24)-((D2-INT(D2))*24) results as £1.17 Difference is 0.43? I have tried other calculations e.g. hour(d2) + minute(d2)/60) etc I am quite baffled can anyone help! If this 1.17 is used in the calculation the result is 151.67 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL GURU WANTED - Time calculations
On Wed, 26 Dec 2007 13:16:00 -0800, Robert
wrote: Problem is when taking a time-start cell from a time-end and applying a rate to the hours I get a different value from doing a simple calculation. ie simple calculation is 1.17 hours @ £130/hour = £152.10 Using times as Start Cell E2 = 13:00 & Cell D2 End 14:10 Calculating as: ((E2-INT(E2))*24)-((D2-INT(D2))*24) results as £1.17 Difference is 0.43? I have tried other calculations e.g. hour(d2) + minute(d2)/60) etc I am quite baffled can anyone help! If this 1.17 is used in the calculation the result is 151.67 Problem is that 14:10 - 13:10 = 70 minutes. 70/60 = 1.16666666666667 (to Excel's level of precision. Since 1.7 is NOT equal to 1.16666666666667 your results cannot be the same. The 1.16666666666667 is mathematically more correct than 1.17. Since we don't know what the office policies are, I don't know which would be more correct for your office. If maximum precision is an issue, then 151.67 is correct. If your office policy is that "all times will be rounded to the nearest 100th of an hour" then the 152.10 is correct. To implement that policy, you could use the formula: =ROUND((EndTm-StartTm)*24,2) * Hourly_Rate --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need an excel guru! HELP! | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Inconsistent excel 2007b2 time calculations that span whole days | Excel Discussion (Misc queries) | |||
Cut and Paste Question for an Excel Guru | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) |