If/then question in time sheet

[Ellen]

Hello,
I've written a time sheet that several people use. I
think they have either Excel 2000 or Excel 2002. The hours
worked are calculated from the "in" and "out" times, which
is in the "H" column. That column is formatted as
[h]:mm.

The leave columns are found in columns J, K, L, M, N and
O. They, too are in the [h]:mm format.

Then I have in the P column "flags" to determine if the
leave was entered in correctly. I use the formula:
=IF(J19+K19+L19+M19+N19+O19=TIME(8,0,0)-H19,"","Check
leave figures"). It seems all is well as long as the time

worked isn't 7:00 and the leave taken is 1:00.

The calculation of the hours worked doesn't seem to be a
factor as I've reproduced this phenomenon without the
calculation.

Thank you in advance to whomever can solve this thing.

Ellen

 

[Peo Sjoblom]

You can round to the nearest minute

=IF(ROUND(SUM(J19:O19)*1440,0)/1/1440=ROUND((TIME(8,0,0)-H19)*1440,0)/1/1440
,"","Check leave figures")

note that it is not necessary to use J19+K19+ and so on

 

[Guest]

You can also round to the 3rd decimal place. This will give you an accuracy of +/- 43 seconds.

=IF(ROUND(J19+K19+L19+M19+N19+O19,3)=Round(TIME(8,0,0)-H19,3),"","Check
leave figures").

I would also suggest using the SUM function as Peo did.

=IF(ROUND(SUM(J19:O19),3)=ROUND(TIME(8,0,0)-H19,3),"","Check leave figures").

Good Luck,
Mark Graesser
(e-mail address removed)

----- Ellen wrote: -----

Hello,
I've written a time sheet that several people use. I
think they have either Excel 2000 or Excel 2002. The hours
worked are calculated from the "in" and "out" times, which
is in the "H" column. That column is formatted as
[h]:mm.

The leave columns are found in columns J, K, L, M, N and
O. They, too are in the [h]:mm format.

Then I have in the P column "flags" to determine if the
leave was entered in correctly. I use the formula:
=IF(J19+K19+L19+M19+N19+O19=TIME(8,0,0)-H19,"","Check
leave figures"). It seems all is well as long as the time

worked isn't 7:00 and the leave taken is 1:00.

The calculation of the hours worked doesn't seem to be a
factor as I've reproduced this phenomenon without the
calculation.

Thank you in advance to whomever can solve this thing.

Ellen

 

[Guest]

Thanks, Mark for the explanation and the solution. I knew
it must be a rounding error, but I didn't know how to fix
it. Now I do.

Ellen

-----Original Message-----
You can also round to the 3rd decimal place. This will

give you an accuracy of +/- 43 seconds.

=IF(ROUND(J19+K19+L19+M19+N19+O19,3)=Round(TIME(8,0,0)- H19,3),"","Check
leave figures").

I would also suggest using the SUM function as Peo did.

=IF(ROUND(SUM(J19:O19),3)=ROUND(TIME(8,0,0)-

H19,3),"","Check leave figures").

Good Luck,
Mark Graesser
(e-mail address removed)

----- Ellen wrote: -----

Hello,
I've written a time sheet that several people use. I
think they have either Excel 2000 or Excel 2002. The hours
worked are calculated from the "in" and "out" times, which
is in the "H" column. That column is formatted as
[h]:mm.

The leave columns are found in columns J, K, L, M, N and
O. They, too are in the [h]:mm format.

Then I have in the P column "flags" to determine if the
leave was entered in correctly. I use the formula:
=IF(J19+K19+L19+M19+N19+O19=TIME(8,0,0)- H19,"","Check
leave figures"). It seems all is well as long as the time

worked isn't 7:00 and the leave taken is 1:00.

The calculation of the hours worked doesn't seem to be a
factor as I've reproduced this phenomenon without the
calculation.

Thank you in advance to whomever can solve this thing.

Ellen

.