#### Chillyfruit

##### New Member

- Joined
- Sep 26, 2011

- Messages
- 15

The context of the spreadsheet is to try to figure out the amount of time the specific ID in column B has worked. It gets complicated when you don't have an even "IN/OUT" distribution and you have more "INs" than "OUTs" but what it should ultimately boil down to is figuring out the allotted time span between the first IN and the last OUT.

This is what the spreadsheet looks like:

Excel Workbook | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | L | M | |||

1 | Time | Event | Device | In/Out Test | In/Out | Hours worked since last swipe | Accumulated Daily Total of Time Working | End of Day Total Time In Office (Hrs) | Day | Violations Flag | Out/In | Hours outside office since last swipe | Accumulated Daily Total of Hours Outside Office | ||

2 | 9/26/2011 13:30 | 100063 7182 | 14-Lobby West IN | IN | 0 | 0.00 | -0.07 | 0.00 | 26 | 0 | 1 | -0.17 | -0.18 | ||

3 | 9/26/2011 13:40 | 100063 7182 | 14-Lobby Hall 14th Fl OUT | OUT | 1 | -0.07 | -0.07 | 0.00 | 26 | 0 | 0 | 0.00 | -0.02 | ||

4 | 9/26/2011 13:44 | 100063 7182 | 14-Lobby West IN | IN | 0 | 0.00 | 0.00 | 0.00 | 26 | 0 | 1 | 0.00 | -0.02 | ||

5 | 9/26/2011 13:44 | 100063 7182 | 14-Lobby Hall 14th Fl OUT | OUT | 1 | 0.00 | 0.00 | 0.00 | 26 | 0 | 0 | 0.00 | -0.02 | ||

6 | 9/26/2011 13:44 | 100063 7182 | 14-Lobby Hall 14th Flr IN | IN | 0 | 0.00 | 0.00 | 0.00 | 26 | 0 | 1 | -0.02 | -0.02 | ||

7 | 9/26/2011 13:45 | 100063 7182 | 14-Lobby West OUT | OUT | 1 | -93.77 | 0.00 | 0.00 | 26 | 0 | 0 | 0.00 | 0.00 | ||

8 | 9/30/2011 11:31 | 100063 7182 | 14-Lobby Hall 14th Flr IN | IN | 0 | 0.00 | 0.00 | 0.00 | 30 | 0 | 1 | 23.48 | 0.00 | ||

9 | 9/30/2011 12:22 | 100063 7182 | 14-Lobby West OUT | OUT | 1 | 0.85 | 0.85 | 0.85 | 30 | 30 | 0 | 0.00 | 0.00 | ||

10 | 9/29/2011 12:02 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -0.65 | 0.00 | 29 | 0 | 1 | -1.80 | -4.70 | ||

11 | 9/29/2011 13:50 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -0.10 | -0.65 | 0.00 | 29 | 0 | 0 | 0.00 | -2.90 | ||

12 | 9/29/2011 13:56 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -0.55 | 0.00 | 29 | 0 | 1 | -0.05 | -2.90 | ||

13 | 9/29/2011 13:59 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -0.45 | -0.55 | 0.00 | 29 | 0 | 0 | 0.00 | -2.85 | ||

14 | 9/29/2011 14:26 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -0.10 | 0.00 | 29 | 0 | 1 | -2.52 | -2.85 | ||

15 | 9/29/2011 16:57 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -0.07 | -0.10 | 0.00 | 29 | 0 | 0 | 0.00 | -0.33 | ||

16 | 9/29/2011 17:01 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -0.03 | 0.00 | 29 | 0 | 1 | -0.10 | -0.33 | ||

17 | 9/29/2011 17:07 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -0.03 | -0.03 | 0.00 | 29 | 0 | 0 | 0.00 | -0.23 | ||

18 | 9/29/2011 17:09 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | 0.00 | 0.00 | 29 | 0 | 1 | -0.23 | -0.23 | ||

19 | 9/29/2011 17:23 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -21.98 | 0.00 | 0.00 | 29 | 0 | 0 | 0.00 | 0.00 | ||

20 | 9/30/2011 15:22 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -0.28 | -0.28 | 30 | 0 | 1 | -0.55 | -2.12 | ||

21 | 9/30/2011 15:55 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -0.12 | -0.28 | 0.00 | 30 | 0 | 0 | 0.00 | -1.57 | ||

22 | 9/30/2011 16:02 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -0.17 | 0.00 | 30 | 0 | 1 | -0.88 | -1.57 | ||

23 | 9/30/2011 16:55 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 1 | -0.17 | -0.17 | 0.00 | 30 | 0 | 0 | 0.00 | -0.68 | ||

24 | 9/30/2011 17:05 | 10012455 7189 | 14-15th main Entry Door IN | IN | 0 | 0.00 | 0.00 | 0.00 | 30 | 0 | 1 | -0.68 | -0.68 | ||

25 | 9/30/2011 17:46 | 10012455 7189 | 14-15th Main Entry Door OUT | OUT | 0 | 0.00 | 0.00 | 0.00 | 30 | 0 | 0 | 0.00 | 0.00 | ||

26 | 9/29/2011 11:17 | 10021422 7316 | 14-15th main Entry Door IN | IN | 0 | 0.00 | -3.52 | -3.52 | 29 | 0 | 1 | -1.27 | -1.27 | ||

27 | 9/29/2011 12:33 | 10021422 7316 | 14-15th Main Entry Door OUT | OUT | 1 | -3.52 | -3.52 | 0.00 | 29 | 0 | 0 | 0.00 | 0.00 | ||

28 | 9/29/2011 16:04 | 10021422 7316 | 14-15th Main Entry Door OUT | OUT | 0 | 0.00 | 0.00 | 0.00 | 29 | 0 | 0 | 0.00 | 0.00 | ||

Sheet2 |

**Excel 2007**

Cell Formulas | ||
---|---|---|

Range | Formula | |

D2 | =RIGHT(C2,3) | |

E2 | =IF(D2="","",IF(B2=B3,IF(EXACT(D2,"OUT"),1,0),0)) | |

F2 | =IF(A2="","",(A2-A3)*24*E2) | |

G2 | =IF($B2="","",IF(I2-I3=0,F2+G3,0)) | |

H2 | =IF($B2="","",IF(G1=0,G2,0)) | |

I2 | =IF($B2="","",DAY(A2)) | |

J2 | =IF($B2="","",IF(H2>8, IF(O2>=1.5, 10, 0), IF(H2<=0, 0, IF(O2>=1.5, 30, 20)))) | |

K2 | =IF($B2="","",IF(EXACT(D2, "OUT"),0,1)) | |

L2 | =IF($B2="","",(A2-A3)*24*K2) | |

M2 | =IF($B2="","",IF(I2-I3=0,L2+M3,0)) |

Excel Workbook | |||||
---|---|---|---|---|---|

N | O | P | |||

1 | End of Day Total (Hrs) | End of Day Total Out of Office (Hrs) | Out of Office for more than 1.5 Hours | ||

2 | 0.00 | 0 | 0 | ||

3 | 0.00 | 0 | 0 | ||

4 | 0.00 | 0 | 0 | ||

5 | 0.00 | 0 | 0 | ||

6 | 0.00 | 0 | 0 | ||

7 | 0.00 | 0 | 0 | ||

8 | 0.00 | 0 | 0 | ||

9 | 0.00 | 0 | 0 | ||

Sheet2 |

**Excel 2007**

Cell Formulas | ||
---|---|---|

Range | Formula | |

N2 | =IF($B2="","",IF(M1=0,M2,0)) | |

O2 | =IF($B2="","",IF(N2>0, N2, "0")) | |

P2 | =IF($B2="","",IF(N2>=0, IF( N2>1.5, 10, 0),0)) |

If anyone would be willing to walk me through this i would really appreciate it. Thanks so much.