Thank you, your explanation did make me take a second look at the cell
formatting just to make sure.

Maybe to help clarify:
Row 13 is the first row, which is used to enter data (as text);
Row 14 is the second row, which converts the text to time or number format
and is used for all the calculations;
Columns K through Q are just simple time subtractions, they do not reference
other cells for input/data.

Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T
0730 1600 8.00 1200 1230 0.50 0900 1300 3.50
07:30 16:00 8.00 12:00 12:30 0.50 09:00 13:00 3.50

The suggested formula didn't work, but it was certainly more my fault for
not "copying" the information properly (R14 & S14 are in time format not text
as shown in my original post).

Maybe rethinking this would be easier... Is there a way for the user to
enter "time" as 0700, 7:00, or 07:00 and Excel treat it all the same? My
biggest problem is trying to get all the "what ifs" in place rather than
force a "you will enter time this way"...

Suggestions?

=IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),TEXT(TEXT((S14-R14),"H:MM")*24-J14,"H:MM"),IF(AND(R14=H14,S14=I14),((S14-R14)*24))))

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------



"BumblebeeFan" wrote:

First thanks for all the great info to get me this far! Think Im close, but
cannot seem to get the time format to show properly. Help?

Trying to subtract €œlunch period€� if partial day leave includes lunch
start/stop times €“ the second row contains the formulas for all the cells.
(Im sure theres a much easier way€¦ <smile)

Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S
Cell T
0730 1600 8.00 1200 1230 0.50 0900 1300 3.50
07:30 16:00 8.00 12:00 12:30 0.50 0900 1300 3.50

Formula:
=IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),(((S14-R14)*24)-J14),IF(AND(R14=H14,S14=I14),((S14-R14)*24))))

Id like the time format to show 3.30 instead of 3.50 and have the *24,
without it the answer is -0.33. Help, please?