Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Time Distribution Formula
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($L260,$L2-SUM(P2)60),60,$L2-SUM(P2)) =IF(AND($L260,$L2-SUM(P2:Q2)60),60,$L2-SUM(P2:Q2)) =IF(AND($L260,$L2-SUM(P2:R2)60),60,$L2-SUM(P2:R2)) =IF(AND($L260,$L2-SUM(P2:S2)60),60,$L2-SUM(P2:S2)) =IF(AND($L260,$L2-SUM(P2:S2)60),60,$L2-SUM(P2:S2)) =IF(AND($L260,$L2-SUM(P2:U2)60),60,$L2-SUM(P2:U2)) =IF(AND($L260,$L2-SUM(P2:V2)60),60,$L2-SUM(P2:V2)) Whe 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Time Distribution Formula
O, just to make the first part easier, make your L2 formula =(K2-J2)*1440.
Format as number. :-) In your remaining formulas: P2: =IF($L260,60,$L2) Q2: =IF(AND($L260,$L2-SUM(P2:Q2)60),60,$L2-SUM(P2:Q2)) etc... "Jules" wrote: 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($L260,$L2-SUM(P2)60),60,$L2-SUM(P2)) =IF(AND($L260,$L2-SUM(P2:Q2)60),60,$L2-SUM(P2:Q2)) =IF(AND($L260,$L2-SUM(P2:R2)60),60,$L2-SUM(P2:R2)) =IF(AND($L260,$L2-SUM(P2:S2)60),60,$L2-SUM(P2:S2)) =IF(AND($L260,$L2-SUM(P2:S2)60),60,$L2-SUM(P2:S2)) =IF(AND($L260,$L2-SUM(P2:U2)60),60,$L2-SUM(P2:U2)) =IF(AND($L260,$L2-SUM(P2:V2)60),60,$L2-SUM(P2:V2)) Whe 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Time Distribution Formula
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. -- Jules "Sean Timmons" wrote: O, just to make the first part easier, make your L2 formula =(K2-J2)*1440. Format as number. :-) In your remaining formulas: P2: =IF($L260,60,$L2) Q2: =IF(AND($L260,$L2-SUM(P2:Q2)60),60,$L2-SUM(P2:Q2)) etc... "Jules" wrote: 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($L260,$L2-SUM(P2)60),60,$L2-SUM(P2)) =IF(AND($L260,$L2-SUM(P2:Q2)60),60,$L2-SUM(P2:Q2)) =IF(AND($L260,$L2-SUM(P2:R2)60),60,$L2-SUM(P2:R2)) =IF(AND($L260,$L2-SUM(P2:S2)60),60,$L2-SUM(P2:S2)) =IF(AND($L260,$L2-SUM(P2:S2)60),60,$L2-SUM(P2:S2)) =IF(AND($L260,$L2-SUM(P2:U2)60),60,$L2-SUM(P2:U2)) =IF(AND($L260,$L2-SUM(P2:V2)60),60,$L2-SUM(P2:V2)) Whe 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Even date time distribution | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
How to Manipulate formula for Binomial Distribution | Excel Worksheet Functions | |||
template or formula for start time -finish time -total hours ple | New Users to Excel |