Help with spreadsheet formulas.


New Member
Sep 26, 2011
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
1TimeEventDeviceIn/Out TestIn/OutHours worked since last swipeAccumulated Daily Total of Time WorkingEnd of Day Total Time In Office (Hrs)DayViolations FlagOut/InHours outside office since last swipeAccumulated Daily Total of Hours Outside Office
29/26/2011 13:30100063 718214-Lobby West ININ00.00-0.070.002601-0.17-0.18
39/26/2011 13:40100063 718214-Lobby Hall 14th Fl OUTOUT1-0.07-0.070.0026000.00-0.02
49/26/2011 13:44100063 718214-Lobby West ININ00.000.000.0026010.00-0.02
59/26/2011 13:44100063 718214-Lobby Hall 14th Fl OUTOUT10.000.000.0026000.00-0.02
69/26/2011 13:44100063 718214-Lobby Hall 14th Flr ININ00.000.000.002601-0.02-0.02
79/26/2011 13:45100063 718214-Lobby West OUTOUT1-93.770.000.0026000.000.00
89/30/2011 11:31100063 718214-Lobby Hall 14th Flr ININ00.000.000.00300123.480.00
99/30/2011 12:22100063 718214-Lobby West OUTOUT10.850.850.85303000.000.00
109/29/2011 12:0210012455 718914-15th main Entry Door ININ00.00-0.650.002901-1.80-4.70
119/29/2011 13:5010012455 718914-15th Main Entry Door OUTOUT1-0.10-0.650.0029000.00-2.90
129/29/2011 13:5610012455 718914-15th main Entry Door ININ00.00-0.550.002901-0.05-2.90
139/29/2011 13:5910012455 718914-15th Main Entry Door OUTOUT1-0.45-0.550.0029000.00-2.85
149/29/2011 14:2610012455 718914-15th main Entry Door ININ00.00-0.100.002901-2.52-2.85
159/29/2011 16:5710012455 718914-15th Main Entry Door OUTOUT1-0.07-0.100.0029000.00-0.33
169/29/2011 17:0110012455 718914-15th main Entry Door ININ00.00-0.030.002901-0.10-0.33
179/29/2011 17:0710012455 718914-15th Main Entry Door OUTOUT1-0.03-0.030.0029000.00-0.23
189/29/2011 17:0910012455 718914-15th main Entry Door ININ00.000.000.002901-0.23-0.23
199/29/2011 17:2310012455 718914-15th Main Entry Door OUTOUT1-21.980.000.0029000.000.00
209/30/2011 15:2210012455 718914-15th main Entry Door ININ00.00-0.28-0.283001-0.55-2.12
219/30/2011 15:5510012455 718914-15th Main Entry Door OUTOUT1-0.12-0.280.0030000.00-1.57
229/30/2011 16:0210012455 718914-15th main Entry Door ININ00.00-0.170.003001-0.88-1.57
239/30/2011 16:5510012455 718914-15th Main Entry Door OUTOUT1-0.17-0.170.0030000.00-0.68
249/30/2011 17:0510012455 718914-15th main Entry Door ININ00.000.000.003001-0.68-0.68
259/30/2011 17:4610012455 718914-15th Main Entry Door OUTOUT00.000.000.0030000.000.00
269/29/2011 11:1710021422 731614-15th main Entry Door ININ00.00-3.52-3.522901-1.27-1.27
279/29/2011 12:3310021422 731614-15th Main Entry Door OUTOUT1-3.52-3.520.0029000.000.00
289/29/2011 16:0410021422 731614-15th Main Entry Door OUTOUT00.000.000.0029000.000.00
Excel 2007
Cell Formulas
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))
Excel Workbook
1End of Day Total (Hrs)End of Day Total Out of Office (Hrs)Out of Office for more than 1.5 Hours
Excel 2007
Cell Formulas
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.

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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))))
Last edited:
Upvote 0
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!
Upvote 0
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.
Upvote 0

Forum statistics

Latest member
Rich Cohen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back