Help with spreadsheet formulas.

[Chillyfruit]

I've been given an excel spreadsheet with formulas that don't really work correctly. I've tried to go in and edit and fix a couple of them but either they failed or i made it worse so i decided to come here and ask for help because you guys are experts at what you do and are kind enough to help.

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.

 

[Chillyfruit]

Just thought now was an appropriate time to bump this.

 

[paddydive]

Hi,

Try it with Max and Min formula...

Max will give you the last OUT time and Min will give your first In Time

Also your formula for J2 is as IF($B2="","",IF(H2>8, IF(O2>=1.5, 10, 0), IF(H2<=0, 0, IF(O2>=1.5, 30, 20))))
however O2 is actually blank, pls check.<TABLE class=html-maker-worksheet border=1 cellSpacing=0 cellPadding=0><TBODY><TR><TH>J2</TH><TD style="WORD-WRAP: break-word">=IF($B2="","",IF(H2>8, IF(O2>=1.5, 10, 0), IF(H2<=0, 0, IF(O2>=1.5, 30, 20))))

 

[Chillyfruit]

Well the formula in J2 is wrong because the formula for N2 is wrong and it cascades down the columns. There's a lot of interdependency amongst the formulas and they are all wrong. I'm not an expert at formulas so can you help with your min max suggestion.

On a side not, i managed to fix the formula for column F
=IF(ISERROR(IF(I2-I1=0,A2-A1)), "-", IF(I2-I1=0,A2-A1,0))

Yay me, a personal achievement!

 

[Chillyfruit]

Too bad you can't edit your messages but i'm making some changes to the formulas across the columns and for column F and G i have :

Column F: =IF(ISERROR(IF(I2-I1=0,A2-A1)*E2), "-", IF(I2-I1=0,A2-A1,0)*E2)
Column G: =IF(ISERROR(IF($B2="","",IF(I2-I1=0,F2+G1,0))),0, IF($B2="","",IF(I2-I1=0,F2+G1,0)))

I have an issue with the formula in column E. It is supposed to place either a 1 for every "OUT" or 0 for every "In" but sometimes it does not place the 1 for the parsed string "OUT"

The formula for Column E as of right now: =IF(D2="","",IF(B2=B3,IF(EXACT(D2,"OUT"),1,0),0))

I need some serious help with the formula in column H; I can't seem to get the logic right.