I dont know whether this post will help you are not. But I am sure that
your most of the data is not in Time format. For example the CellR, Cell J
and Cell T values are something like Numberformat & Text format. First make
the data in correct format. Apart from that you have not mentioned what data
you are having in S14 and R14. Then finally for finding the difference
between two different times you are using (S14-R14)*24 I dont know why you
should not use the Time format (i.e.) =TEXT((S14-R14),"H:MM") to Convert it
to Time format.

The below mentioned formula is not perfect, because I dont know what values
are you are having in cells S14 & R14 and what you are looking for, But have
a look at it.

=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?