Adding times but ignoring text.

[RollieMillan]

Hi, I'm trying to find a formula that will add different times but ignore cells when text instead of time is typed on the cell. Here is an example of what I'm trying to do and the formula I'm currently using (which works until text is replacing time):

[K] [L] [N] [O] [Q] [R] [T] [W] [X] [Z]
MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY TOTAL HRS
In Out In Out In Out In Out In Out
5:00 AM 2:00 PM 5:00 AM 12:00 PM 5:00 AM 4:00 PM OFF OFF 5:00 AM 1:30 PM _________

Formula used: =SUM((L3-K3)*24)+((O3-N3)*24)+((R3-Q3)*24)+((U3-T3)*24)+((X3-W3)*24)

I was privileged to keep track of the times and days off of 500+ employees, so I'm trying to make my life as easy as possible. Thank you in advance for your help.

 

[FDibbins]

don't put the *24 in each calc, do it at the end...
=((L3-K3)+(O3-N3)+(R3-Q3)+(U3-T3)+(X3-W3))*24

 

[jtakw]

Hi,

I think this will work for you, use formula in Z5:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>K</th><th>L</th><th>N</th><th>O</th><th>Q</th><th>R</th><th>T</th><th>U</th><th>W</th><th>X</th><th>Y</th><th>Z</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5:00 AM</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">5:00 AM</td><td style="text-align: right;;">12:00 PM</td><td style="text-align: right;;">5:00 AM</td><td style="text-align: right;;">4:00 PM</td><td style=";">OFF</td><td style=";">OFF</td><td style="text-align: right;;">5:00 AM</td><td style="text-align: right;;">1:30 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">35.5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Z3</th><td style="text-align:left">=SUM(<font color="Blue">(<font color="Red">L3-K3</font>)*24</font>)+(<font color="Blue">(<font color="Red">O3-N3</font>)*24</font>)+(<font color="Blue">(<font color="Red">R3-Q3</font>)*24</font>)+(<font color="Blue">(<font color="Red">U3-T3</font>)*24</font>)+(<font color="Blue">(<font color="Red">X3-W3</font>)*24</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Z4</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">L3-K3</font>)+(<font color="Red">O3-N3</font>)+(<font color="Red">R3-Q3</font>)+(<font color="Red">U3-T3</font>)+(<font color="Red">X3-W3</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Z5</th><td style="text-align:left">=(<font color="Blue">IFERROR(<font color="Red">L3-K3,0</font>)+IFERROR(<font color="Red">O3-N3,0</font>)+IFERROR(<font color="Red">R3-Q3,0</font>)+IFERROR(<font color="Red">U3-T3,0</font>)+IFERROR(<font color="Red">X3-W3,0</font>)</font>)*24</td></tr></tbody></table></td></tr></table><br />

 

[shg]

Or

=(SUM(L3,O3,R3,U3,X3) - SUM(K3,N3,Q3,T3,W3))*24

 

[FDibbins]

ADVERTISEMENT

hmm good option, shg

 

[RollieMillan]

GENIUS, pure Genius. This worked perfectly. Thank you so much JTAKW.

 

[FDibbins]

ADVERTISEMENT

Using JT/s sample, I can see that mine did not work, but shg's did and is far simpler

 

[jtakw]

GENIUS, pure Genius. This worked perfectly. Thank you so much JTAKW.

You're welcome, welcome to the forum, just realized you're not far from me, (couple hours drive).

I agree, shg's formula is a more elegant solution, check it out.

I actually started constructing my formula in that same manner, but must have messed something up, and it didn't work the way I wrote it, so I provided the formula in post #3, but again, I recommend shg's formula.

 

[Peter_SSs]

Given that you have column headings, you could also use those to get the sums. Whilst this is slightly longer than shg's & it seems your data is just 5 days, this would also allow easy expansion to a bigger range without targeting individual cells/columns.

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	In	Out		In	Out		In	Out		In	Out		In	Out
3	5:00 AM	2:00 PM		5:00 AM	12:00 PM		5:00 AM	4:00 PM		OFF	OFF		5:00 AM	1:30 PM		35.5
Sheet4