EXCEL GURU WANTED - Time calculations

[Robert]

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

 

[T. Valko]

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

 

[Chip Pearson]

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)

 

[ilia]

Rounding error. Using this formula will give you the correct result:

=ROUND(HOUR(E2-D2)+MINUTE(E2-D2)/60,2)

Hope that helps.

 

[Ron Rosenfeld]

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