Formula to ignore empty cells when calculating Time

[stacyl14]

First off, I am new to this forum and I just want to thank every one in advance for any and all assistance. I have been searching the web for a week and have not even come close to figuring this out. Here is my problem: We have created a excel (2010) workbook to track workers who are late (see below) to determine lost production. I have managed to get it to calculate the time but only if I fill every cell with either the time the worker arrived or the scheduled work time. This throws off my calculation in column "Q" which I have set to calculate the number of times the worker has been late during the pay period (counts cells with data in them). What I need is a formula which will calculate the time difference (between arrival time and actual start time) in only the cells that have a time entered and ignoring the cells with no time entered, then total them in Column "R" (formatted [h]:mm) . Monday through Friday (columns: E,F,G,H,I,K,L,M,N,O) the start time is 07:30 (located in cell: W2), on Saturdays (columns: J,P) the start time is 06:30 (located in cell: X2).

I hope someone here can understand what I am trying to say.

Excel 2010

	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
Mon-Fri/ Start	Sat/ Start

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: center"]2-Sep[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3-Sep[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4-Sep[/TD]
[TD="align: center"]5-Sep[/TD]
[TD="align: center"]6-Sep[/TD]
[TD="bgcolor: #DA9694, align: center"]6-Sep[/TD]
[TD="bgcolor: #FFFFFF, align: center"]9-Sep[/TD]
[TD="bgcolor: #FFFFFF, align: center"]10-Sep[/TD]
[TD="bgcolor: #FFFFFF, align: center"]11-Sep[/TD]
[TD="align: center"]12-Sep[/TD]
[TD="align: center"]13-Sep[/TD]
[TD="bgcolor: #DA9694, align: center"]14-Sep[/TD]
[TD="bgcolor: #FFFF00, align: center"]TOTAL[/TD]
[TD="align: center"]Minutes Late[/TD]
[TD="align: center"]Lost Production Rate[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Lost Production Value[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Total Lost Production[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]7:35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: right"]6:50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8:05[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$82.80[/TD]
[TD="bgcolor: #FF0000, align: center"]0[/TD]
[TD="bgcolor: #FF0000, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]6:30[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]=COUNTA(E2:F2:G2:H2:I2:J2:K2:L2:M2:N2:O2:P2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q3[/TH]
[TD="align: left"]=COUNTA(E3:F3:G3:H3:I3:J3:K3:L3:M3:N3:O3:P3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q4[/TH]
[TD="align: left"]=COUNTA(E4:F4:G4:H4:I4:J4:K4:L4:M4:N4:O4:P4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]T2[/TH]
[TD="align: left"]=R2*$S$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]U2[/TH]
[TD="align: left"]=SUM(T2:T155)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]T3[/TH]
[TD="align: left"]=R3*$S$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]T4[/TH]
[TD="align: left"]=R4*$S$2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[stacyl14]

MikeWx,

I'm such a moron, I had the date set wrong in that column (J). Thanks a million for all of your help. Every thing is working perfectly now. I don't know if there is a way to give reputation points on this site, but you get a 100% rating in my book. Again, Thanks for all of your help.

 

[MikeWx]

I'm glad to here it is working the way you need it to. Thanks for your feedback.