Incremental time values based upon clock in and clock out times

[saltnsnails]

I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if anyone
else had input. I know it is generally frowned upon to report the same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is that
originally the time ins and time outs started in column E. We had to add one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if you had
the original formula before the added time columns, it might help you or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10>($A43),IF($E$10>$B43,0,IF($A43>=$E$10,IF($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10>$A43,($B43-$E$10)*1440/60))),0)+IF($H$10>($A43),IF($G$10>$B43,0,IF($A43>=$G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10>$B43,($B43-$G$10)*1440/60))),0)+IF($J$10>($A43),IF($I$10>$B43,0,IF($A43>=$I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10>$B43,($B43-$I$10)*1440/60))),0)

 

[T. Valko]

I haven't worked out the exact formula just yet but on closer inspection
this won't be that complicated but the formula will be kind of long but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

 

[T. Valko]

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440

 

[saltnsnails]

Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns a value
for each time period therefore resulting in a 1 as the result. Do we need
some if statements to qualify each segment of the formula?

 

[T. Valko]

You have to account for any empty cells in the Time In/Time Out range.

That makes the formula a little bit longer. Here's a small sample file that
demonstrates this.

xTime.xls 17kb

http://cjoint.com/?bnfbSoOJc7

I've also used rounding to 2 decimal places.

 

[saltnsnails]

Valko!
You are awesome!!! I got this to work when I copied down the first column
within the same name but I am having difficulty pulling it across rows as the
"names" change. Any thoughts??? Thanks!

 

[T. Valko]

That's where the real complication comes into play.

I used the same formula for each name and then manually changed the row
references. You could write the formula to do this automatically but this is
where it will get much more complicated and the length of the formula will
grow dramatically to the point where it's no better than your original
formula.

If you want to go that route let me know and I'll tweak it.

 

[saltnsnails]

Valko,
Not necessary. I came to the conclusion myself and I went through and hand
edited each formula. It wasn't too bad since there were only 16 columns.
Once I got the heading formula correct I could copy down into the 40 or so
cells below it.

I can't tell you how much I appreciate your help! I would have spent weeks
figuring this out. Keep up the great work with helping all of us lost Excel
souls out there!

 

[T. Valko]

You're welcome. Thanks for the feedback!