On add to Formula to return a 1 or 0

[Stef9910]

Hello Awesome People of the is forum,

I am after some assistance if anyone knows how to do this,

I have (with the help from Fluff on this site, thank you Fluff) made great progress with my workbook, but in the below where it has the 45k numbers I would like it to display a 1 or 0, the existing formula in the cell needs to stay the same as it is used in all cells, so wondering if would be possible to add on the existing formula to display a 1 or 0,

All the data entered is in the correct place and cannot be moved, or it messes up the rest of the spreadsheet,

I understand that the 45k number is displaying because there is a date in the J9 (the start date) which needs to remain in place.

It is only the rows where it says Scheduled that the 45k numbers displays on.

Also if possible, on the same row, I would like the percentage to display 100%, so would need an add on to that formula also.

Is this possible?

Any help will be greatly appreciated, thank you

Stefan

Formula add on.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2
3														Do not Enter Data Here
4	Type	Status	Within Timeframes	Reported	Number	Recived Date	Received Time	Start Date	Start Time	End Date	End Time	RTP Date	RTP Time	Days until Started	Span	Start to End Days	% Facilitated
5	Unscheduled	Completed	Yes	Yes	2303/0124	31/12/22	19:16	31/12/22	19:45	30/4/23				0	120	120	100%
6	Unscheduled	Completed	No	Yes	2303/0395	29/3/23	21:23	30/3/23	17:45	20/4/23	16:06			1	22	21	95%
7	Unscheduled	Completed	Yes	No		2/4/23	15:31	2/4/23	16:30	30/4/23				0	28	28	100%
8	Unscheduled	Completed	Yes	No		2/4/23	20:42	2/4/23	21:45	4/4/23	15:59			0	2	2	100%
9	Scheduled	Completed	Yes	No				3/4/23	6:11	4/4/23	13:39			45019	45020	1	0%
10	Scheduled	Completed	Yes	No				4/4/23	6:45	4/4/23	10:56			45020	45020	0	0%
11	Unscheduled	Completed	Yes	No		4/4/23	1:36	4/4/23	4:00	7/4/23	16:34			0	3	3	100%
12	Scheduled	Completed	Yes	No				5/4/23	6:22	6/4/23	19:58			45021	45022	1	0%
13	Unscheduled	Not Completed	No	No	2304/0044	5/4/23	4:37					5/4/23	10:28	0	0	0	0%
14	Unscheduled	Not Completed	No	No	2304/0043	5/4/23	3:39					5/4/23	17:47	0	0	0	0%
15	Unscheduled	Completed	No	No	2304/0042	5/4/23	3:38	7/4/23	20:15	30/4/23				2	25	23	92%
16	Unscheduled	Completed	No	No	2304/0020	3/4/23	23:09	4/4/23	11:55	5/4/23	17:55			1	2	1	50%
Sheet1

Cell Formulas
Range	Formula
P5:P16	P5	=IF(N5="",J5-H5,0)
Q5:Q16	Q5	=IF(N5<>"",N5,L5)-H5
R5:R16	R5	=IF(N5="",L5-J5,0)
S5:S16	S5	=IFERROR(R5/Q5, 0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S5:S16	Other Type	DataBar		NO
G14:G16	Expression	=E26="no"	text	NO

 

[awoohaw]

what is the condition for you to choose a 1 or 0?

 

[Fluff]

What should it display if col H is blank?

 

[Stef9910]

If column H is blank, column P needs to display a 0 and column Q needs to display the same as column R

The condition would whether column C has scheduled in it.

Thank you for your quick responses.

 

[Fluff]

Ok, for P5

=IF(OR(H5="",N5<>""),0,J5-H5)

 

[Stef9910]

Ok, for P5

=IF(OR(H5="",N5<>""),0,J5-H5)

That worked awesome thank you Fluff

 

[Stef9910]

That worked awesome thank you Fluff

Using that that formula in column Q but changing J to L worked brilliantly also, thank you,

Just need the percentage in column S to show 100% now.

 

[Stef9910]

Using that that formula in column Q but changing J to L worked brilliantly also, thank you,

Just need the percentage in column S to show 100% now.

Doh, no it didn't work in column Q, column Q needs to show the same as column R

 

[Fluff]

Why should Q9 be 1 but Q10 be 0?

 

[Stef9910]

Why should Q9 be 1 but Q10 be 0?

Q10 should be 1 also

You have identified another problem, R10 should show 1, but for some reason a job in and out on the same day is show 0 days, I would like it to show 1