Time Differential Error???

[Guest]

I am working on a timesheet in Excel and seem to have come upon a strange
quirk/error. The timesheet is supposed to take two times, find the difference
and then display it in decimal format.

For example:
A1 = 09:00
A2 = 09:30
A3 = 0.5

The formula I have in cell A3 for making this computation is:

ROUNDUP((A2-A1)*1440/60,1)

The idea of the roundup is so that as soon as you work more than a specific
tenth of an hour you get credit for the next tenth. The breakdown of an hour
would be as follows:

..1 = 1-6 minutes
..2 = 7-12
..3 = 13-18
..4 = 19-24
..5 = 25-30
..6 = 31-36
..7 = 37-42
..8 = 43-48
..9 = 49-54
1.0 = 55-60

The issue I am having occurs for time differences of 30 minutes but only at
specific times. One of these ranges for example is 14:30 to 15:00. When the
formula calculates the time difference it comes out incorrectly to just over
..5 so it is rounded up to .6.

Does anyone have any ideas on how to workaround this or why Excel does this?

Thanks,
The Merg

 

[Guest]

Tools > Formula Auditing > Evaluate Formula, you'll find you get
ROUNDUP((0.625-0.604166666666667)*1440/60,1), which =
ROUNDUP((0.020833333333334)*1440/60,1) =
ROUNDUP((30.00000000001)/60,1) =
ROUNDUP((5.00000000001,1) =
0.6

Try this instead:
ROUNDUP((ROUND(C2,4)-ROUND(C1,4))*1440/60,1)

rounding to however many decimals you want

hope this helps

 

[David Biddulph]

I am working on a timesheet in Excel and seem to have come upon a strange
quirk/error. The timesheet is supposed to take two times, find the
difference
and then display it in decimal format.

For example:
A1 = 09:00
A2 = 09:30
A3 = 0.5

The formula I have in cell A3 for making this computation is:

ROUNDUP((A2-A1)*1440/60,1)

The idea of the roundup is so that as soon as you work more than a
specific
tenth of an hour you get credit for the next tenth. The breakdown of an
hour
would be as follows:

.1 = 1-6 minutes
.2 = 7-12
.3 = 13-18
.4 = 19-24
.5 = 25-30
.6 = 31-36
.7 = 37-42
.8 = 43-48
.9 = 49-54
1.0 = 55-60

The issue I am having occurs for time differences of 30 minutes but only
at
specific times. One of these ranges for example is 14:30 to 15:00. When
the
formula calculates the time difference it comes out incorrectly to just
over
.5 so it is rounded up to .6.

Does anyone have any ideas on how to workaround this or why Excel does
this?

This is likely to be yet another example of where calculations involving
decimal numbers gives ansswers which don't come out quite right. You need
to remember that Excel times are stored as fractions of a day, so 06:00 will
be 0.25, and secondly you need to remember that Excel works in a binary
representation, so while it can represent numbers such as 0.5, 0.25, 0.125,
0.375, etc. it can't give an exact representation of numbers like 0.1.
Excel works to 15 significant figures, but there will be small inaccuracies.

Your 30 minutes is 0.20833333... of a day, so can't even be represented
accurately in decimal, never mind in binary.

 

[Guest]

Or, you could write it this way:

ROUNDUP((A2*24-A1*24),1)

this is much simpler.

 

[Guest]

I went with this route ROUNDUP((A2*24-A1*24),1) to solve the issue. I had
gone through the Forumula Evaluation and saw that when the difference between
the times was 0.020833333333334 and not 0.020833333333333 is when I had the
issue. I guess multiplying the times by 24 prior to computing the difference
renders the issue moot.

Thanks for the help.

- Merg