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

 

[Stef9910]

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

If Q displayed the same as R I'm pretty sure that will sort out the percentage column itself!

 

[Fluff]

Ok, this will put 1 into the cell if H is blank

=IF(H5="",1,IF(N5<>"",N5,L5)-H5)

 

[Stef9910]

Ok, this will put 1 into the cell if H is blank

=IF(H5="",1,IF(N5<>"",N5,L5)-H5)

Fluff, you are amazing, worked a treat, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Stef9910]

You're welcome & thanks for the feedback.

Hi Fluff,

Just thought i would let you know that through you help with the above, I have solved R10 with =IF(N10="", IF(L10=J10,1,L10-J10), 0) and for Q13 by using =IF(H13="", 1, IF(L13="", IF(N13=H13, 1, N13-H13), IF(N13<>"", N13, L13)-H13))

Again thank you for all your help, it really has helped me