J
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
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