Trying to make formula ignore text

[thatguydj]

Hello I'm working on making a calculator to calculate hours in a schedule. I want any boxes without numbers to just say "OFF" for employees, but I am having issues with making excel ignore that text.

I'm not amazing at functions, but I have the whole schedule working except for the words. There is a second portion of the schedule that subtracts break time from total hours, in case the second value looks confusing. It is lower in the list. Does anyone have any ideas to make the formula ignore the word "OFF"?

 

[6StringJazzer]

One solution is to enclose every cell reference with the N function. This will return the number if it's a number, and return 0 if it's text.

By the way you do not need the SUM function for what you are doing. You can just remove it.

$scratch.xlsm
	A	B	C	D	E
1	Sunday		Monday		Total Hours
2	1:00 PM	5:00 PM	OFF	OFF	4
Sheet11

Cell Formulas
Range	Formula
E2	E2	=((N(B2)-N(A2))+(N(D2)-N(C2)))*24

 

[thatguydj]

Thank you this worked perfectly.

 

[6StringJazzer]

Here is a much better way.

If you use a cell in an arithmetic expression, and that cell has text, it will cause a #VALUE error. But if you use only SUM, it will be ignored. Your original formula used SUM but it also used an arithmetic expression inside SUM. If we use only SUM with no arithmetic, it works.

Note that the leap of logic here is that we can SUM all of the ending times and subtract the SUM of all the beginning times. You do not have to calculate each day separately.

You will have to add the bit about row 59 similarly.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday		Total Hours
2	1:00 PM	5:00 PM	1:00 PM	5:00 PM	OFF	Off	1:00 PM	5:00 PM	1:00 PM	5:00 PM	OFF	OFF	1:00 PM	5:00 PM	20
Sheet11

Cell Formulas
Range	Formula
O2	O2	=24*(SUM(N2,L2,J2,H2,F2,D2,B2)-SUM(M2,K2,I2,G2,E2,C2,A2))