If Statement to subtract “time†if condition met

[BumblebeeFan]

First thanks for all the great info to get me this far! Think I’m 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.
(I’m sure there’s 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))))

I’d 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?

 

[Ms-Exl-Learner]

I don’t 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 don’t 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 don’t 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!

 

[dellosa]

First thanks for all the great info to get me this far! Think I’m 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.
(I’m sure there’s 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))))

I’d 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?

Hi Bumblebeefan: please use the right cell formatting.. "HH:mm" it will
solve your problem.

 

[dellosa]

First thanks for all the great info to get me this far! Think I’m 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.
(I’m sure there’s 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))))

I’d 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?

 

[BumblebeeFan]

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?

 

[BumblebeeFan]

Got it to work using:

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

and setting format to [h].mm

Thanks for your time and help; looking at other posts and trying different
things finally worked.

 

[David Biddulph]

You seem to have some unnecessary functions in there.
OR(AND(R14=""),AND(S14="")) is just
OR(R14="",S14="")

 

[BumblebeeFan]

Thank you! I wasn't sure about the "or/and" and if it mattered -- copied the
formula from another post. Thank you, this will certainly help in the future!