Error in subtracting time - Help
 

I have a formula that is subtracting time and when the person works more than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!

Error in subtracting time - Help
 

In your posted example

StartTime: 9:00 AM
EndTime: 2:50 PM

Total Time: 5 hours and 50 minutes
Less: 30 minutes
Equals: 5 hours 20 minutes

That's what your formula returns.

Where are you seeing an error?
***********
Regards,
Ron

XL2003, WinXP


"mndpy" wrote:

I have a formula that is subtracting time and when the person works more than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!

Error in subtracting time - Help
 

I see no error the times you give do equate to 5:20

Please clarify.

Mike

"mndpy" wrote:

I have a formula that is subtracting time and when the person works more than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!

Error in subtracting time - Help
 

Why do you say it is incorrect? It seems to be doing what you've asked it
to do. The time difference is 5:50, which is greater than 4.99 hours, so it
subtracts an extra 30 minutes, giving 5:20.

What did you expect it to do?
--
David Biddulph

"mndpy" wrote in message
...
I have a formula that is subtracting time and when the person works more
than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when
the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!

Error in subtracting time - Help
 

Your formula is working fine.
Try the formula below. It's much shorter than the one you have.

=K20-K19-(K20-K194.99/24)*0.5/24


"mndpy" wrote:

I have a formula that is subtracting time and when the person works more than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!

Error in subtracting time - Help
 

Don't forget to wrap your formulas in the MOD functions:

=MOD(K20-K19-(MOD(K20-K19,1)4.99/24)*0.5/24,1)

Without MOD, midnight crossovers become an issue. If the work starts at 11
PM and ends at 5 AM the next day (6 hours), your formula returns a negative
time value (-0.75) instead of 5.5 hours.
***********
Regards,
Ron

XL2003, WinXP


"Teethless mama" wrote:

Your formula is working fine.
Try the formula below. It's much shorter than the one you have.

=K20-K19-(K20-K194.99/24)*0.5/24


"mndpy" wrote:

I have a formula that is subtracting time and when the person works more than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!