picklefactory
Well-known Member
- Joined
- Jan 28, 2005
- Messages
- 506
- Office Version
- 365
- Platform
- Windows
Hi folks
Frying my brain with this. My head says it should be simple....unfortunately it seems to be me that's simple
I have some software that logs machine activity and creates a lot of data that I need to summarise, very small snapshot enclosed. Cols A to E are what comes from the software and it tracks machine status over entire 24 hour periods, some of which time the machine is unmanned and not running. I'm trying to extract the time spent running during the two shift periods (Early and Late) where it is manned, so in Cols K,L,O and P I have constructed shift start and end times for each day that the log has recorded and I simply want to sum the Mins from Col D where the start/end time in cols B & C fall between the start/end times in Cols K,L,O and P for each shift PLUS the LogID in Col E = 2 (Which is the log code for running). So I think 3 criteria, but every formula attempt I've tried so far has failed miserably. I know in this snapshot enclosed that the Early shift will show nothing, as the times fall outside of the window due to the log start/end times in this instance.
Is it simple and I'm just thick? It's not an array formula, is it?
Thanks
Frying my brain with this. My head says it should be simple....unfortunately it seems to be me that's simple
I have some software that logs machine activity and creates a lot of data that I need to summarise, very small snapshot enclosed. Cols A to E are what comes from the software and it tracks machine status over entire 24 hour periods, some of which time the machine is unmanned and not running. I'm trying to extract the time spent running during the two shift periods (Early and Late) where it is manned, so in Cols K,L,O and P I have constructed shift start and end times for each day that the log has recorded and I simply want to sum the Mins from Col D where the start/end time in cols B & C fall between the start/end times in Cols K,L,O and P for each shift PLUS the LogID in Col E = 2 (Which is the log code for running). So I think 3 criteria, but every formula attempt I've tried so far has failed miserably. I know in this snapshot enclosed that the Early shift will show nothing, as the times fall outside of the window due to the log start/end times in this instance.
Is it simple and I'm just thick? It's not an array formula, is it?
Thanks
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3 | =I3 | |
| J4 | =IF(J3="","",IF(J3+1>($I$4+1),"",J3+1)) | |
| J5 | =IF(J4="","",IF(J4+1>($I$4+1),"",J4+1)) | |
| J6 | =IF(J5="","",IF(J5+1>($I$4+1),"",J5+1)) | |
| J7 | =IF(J6="","",IF(J6+1>($I$4+1),"",J6+1)) | |
| J8 | =IF(J7="","",IF(J7+1>($I$4+1),"",J7+1)) | |
| J9 | =IF(J8="","",IF(J8+1>($I$4+1),"",J8+1)) | |
| J10 | =IF(J9="","",IF(J9+1>($I$4+1),"",J9+1)) | |
| K3 | =IF(J3="","",J3+$I$5) | |
| K4 | =IF(J4="","",J4+$I$5) | |
| K5 | =IF(J5="","",J5+$I$5) | |
| K6 | =IF(J6="","",J6+$I$5) | |
| K7 | =IF(J7="","",J7+$I$5) | |
| K8 | =IF(J8="","",J8+$I$5) | |
| K9 | =IF(J9="","",J9+$I$5) | |
| K10 | =IF(J10="","",J10+$I$5) | |
| L3 | =IF(J3="","",J3+$I$6) | |
| L4 | =IF(J4="","",J4+$I$6) | |
| L5 | =IF(J5="","",J5+$I$6) | |
| L6 | =IF(J6="","",J6+$I$6) | |
| L7 | =IF(J7="","",J7+$I$6) | |
| L8 | =IF(J8="","",J8+$I$6) | |
| L9 | =IF(J9="","",J9+$I$6) | |
| L10 | =IF(J10="","",J10+$I$6) | |
| O3 | =IF(J3="","",J3+$I$7) | |
| O4 | =IF(J4="","",J4+$I$7) | |
| O5 | =IF(J5="","",J5+$I$7) | |
| O6 | =IF(J6="","",J6+$I$7) | |
| O7 | =IF(J7="","",J7+$I$7) | |
| O8 | =IF(J8="","",J8+$I$7) | |
| O9 | =IF(J9="","",J9+$I$7) | |
| O10 | =IF(J10="","",J10+$I$7) | |
| P3 | =IF(J3="","",J3+$I$8) | |
| P4 | =IF(J4="","",J4+$I$8) | |
| P5 | =IF(J5="","",J5+$I$8) | |
| P6 | =IF(J6="","",J6+$I$8) | |
| P7 | =IF(J7="","",J7+$I$8) | |
| P8 | =IF(J8="","",J8+$I$8) | |
| P9 | =IF(J9="","",J9+$I$8) | |
| P10 | =IF(J10="","",J10+$I$8) | |
| I3 | =INT(MIN(B:B)) | |
| I4 | =INT(MAX(B:B)) | |
Last edited:
