Help with Time Distribution Formula

[Jules]

Greetings All,

I use a very preplexing formula to get to some time distribution....I need
to know the exact hour utilization by the start and stop times...I'm having a
problem when the start and stop time are within the same hour of day.
Otherwise this formula has been great....

=IF(HOUR(K2)=HOUR(J2),MINUTE(J2),60-MINUTE(J2))
=IF(AND($L2>60,$L2-SUM(P2)>60),60,$L2-SUM(P2))
=IF(AND($L2>60,$L2-SUM(P2:Q2)>60),60,$L2-SUM(P2:Q2))
=IF(AND($L2>60,$L2-SUM(P2:R2)>60),60,$L2-SUM(P2:R2))
=IF(AND($L2>60,$L2-SUM(P2:S2)>60),60,$L2-SUM(P2:S2))
=IF(AND($L2>60,$L2-SUM(P2:S2)>60),60,$L2-SUM(P2:S2))
=IF(AND($L2>60,$L2-SUM(P2:U2)>60),60,$L2-SUM(P2:U2))
=IF(AND($L2>60,$L2-SUM(P2:V2)>60),60,$L2-SUM(P2:V2))


Where:
K2 = Time Out
J2 = Time In
L2 = Total Minutes
P2 = First Hour
Q2 = Second Hour
And so on….

Can anyone help with the issue of the start and stop time being within the
same hour...right now it's distributing time like this

J2 K2 L2 P2 Q2
07:28 07:53 25 28 -3

This seems to only occurr when the start and stop time are in the same hour.

Any help would be great!
Thanks,
Jules

 

[Sean Timmons]

O, just to make the first part easier, make your L2 formula =(K2-J2)*1440.
Format as number.

In your remaining formulas:

P2:
=IF($L2>60,60,$L2)
Q2:
=IF(AND($L2>60,$L2-SUM(P2:Q2)>60),60,$L2-SUM(P2:Q2))
etc...

 

[Jules]

Hi Sean, this did not work for me...it is puttling all of the time in the
first hour (p2)...I need it spead from P2- AB2)

So the case is starting at 7:20 and stoping at 8:12 and it's giving me 52
minutes in the first hour...when infact it is 40 in (p2) and 12 minutes in
(Q2).

Any other suggestions?

Thanks.