Help with Excel Formula

[jbobzien]

I have been given the task of updating our hrly tracking of employees at
my job. We have lovely excel sheets that do this, however we have old
ones that convert the time from hrs and minutes to hours and two
decimal places. We put them into a new spreadsheet (why? I don't know!)
by hand. They want me to merge these two ideas. Easy right? well the old
ones were written by some guy that doesn't work here anymore,
and.......... they are all pwd protected! I have to use the format on
the new spreadsheet. Now his formula, while hard to extract, was
finally extracted and I'll show it to you now:

=ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2)

Apparently it takes two cells that are in TIME format, subtracts them,
then adds them to two more cells and gives you a total, which is all
correct. However, in the new spread sheet, I have..... Time in, Time
out, and Lunch... so I need to get rid of the 2nd set of two cells, and
put in a simple subtract cell if there's time in it (for lunch).
Unfortunately the old guy knew a LOT more about excel than me, and I'm
getting a headache from looking at that formula. Can anyone help?

--John

 

[hansyt]

I don't quite understand what you want to achieve but read the formula
like this:

=ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2)



The result in the active cell will be (rounded to 2 decimal places)

if b15 = "" or rc15 = ""
then 0
else
if c15 < b15
then (c15 - B15) * 24 + 24
else (C15 - B15) * 24 + 0 if E15 = "" or F15 = ""
else + (F15-E15) * 24 + 24 if F15 < E15
else + (F15-E15) * 24


Hans

 

[jbobzien]

Once you broke it down, I could understand it.
The original formula subtracted two cells times, then added that to
another set of subtracted cells. I needed it to just add two cell
times, then subtract another cell. I sorta cheated (-G6) and did this:

=ROUND(IF((OR(E6="",F6="")),0,IF((F6<E6),((F6-E6)*24)+24,(F6-E6)*24)-G6),2)

(*The cell numbers changed as I put them into my spreadsheet, thus
E6=B15 and F6=C15 in the orignally posted formula)

Now I have another question, I don't want it to display 0 in all the
cells that have nothing in them yet..........

 

[daddylonglegs]

This should do what you want, slightly simplified

=IF(E6*F6,ROUND(MOD(F6-E6,1)*24-G6,2),"")

 

[jbobzien]

That's exactly what I wanted!

--Joh