blank cell minus date

[aliaslamy2k]

Hello,

How Can I avoid this number “-43735, -43689 “, I am subtracting date from date. If any cell is blank then the result should come as blank.

Thank you

Recruitment Dashboard - Ali - 3 - Live V0.01.xlsx
	C	D	E	F
13	Process	Dates	Days	Days from App rcvd
14	ERF RECEIVED DATE	05-Aug-2019
15	APPLICATION RCVD DT	12-Aug-2019	7
16	PHONE INT BY HR	15-Aug-2019	3	3
17	FWD TO BUSINESS	17-Sep-2019	33	36
18	RPLY RCVD FRM BSNESS	19-Sep-2019	2	38
19	TEL INT BY BSNESS	25-Sep-2019	6	44
20	FEEDBCK FRM BSNESS	27-Sep-2019	2	46
21	F2F INT BY BSNESS		-43735	-43689
22	FEEDBCK FRM BSNESS		0	-43689
23	HIRE/REJECTED DT		0	-43689
24	OFFER SENT TO CANDT		0	-43689
25	OFFER ACPT BY CANDT		0	-43689
26	VISA INITITATE DT		0	-43689
27	VISA ISSUE DATE		0	-43689
28	SENT TO CANDT		0	-43689
29	TKT ISSUE DT		0	-43689
Report

 

[cyrilbrd]

Try row 16 =IFERROR(D16-D15,"") and =IFERROR(D16-$D$15,"")

 

[aliaslamy2k]

Hi, Cyrildrd,

i can still find the number reflecting in cell F21. Please find attached for your reference.

Recruitment Dashboard - Ali - 3 - Live V0.01.xlsm
	C	D	E	F
13	Process	Dates	Days	Days from App rcvd
14	ERF RECEIVED DATE	05-Aug-2019
15	APPLICATION RCVD DT	12-Aug-2019	7
16	PHONE INT BY HR	15-Aug-2019	3	3
17	FWD TO BUSINESS	17-Sep-2019	33	33
18	RPLY RCVD FRM BSNESS	19-Sep-2019	2	2
19	TEL INT BY BSNESS	25-Sep-2019	6	6
20	FEEDBCK FRM BSNESS	27-Sep-2019	2	2
21	F2F INT BY BSNESS		-43735	-43735
22	FEEDBCK FRM BSNESS		0	0
23	HIRE/REJECTED DT		0	0
24	OFFER SENT TO CANDT		0	0
25	OFFER ACPT BY CANDT		0	0
26	VISA INITITATE DT		0	0
Report

 

[footoo]

What is returned by : =D21=""

 

[aliaslamy2k]

Actually, i want the result to be blank if one or both cells are blank

 

[sandy666]

try
E15: =IF(OR(D14="",D15=""),"",D15-D14)
F16: =IF(OR(D15="",D16=""),"",D16-$D$15)

Book4
	C	D	E	F
13	Process	Dates	Days	Days from App rcvd
14	ERF RECEIVED DATE	05-Aug-19
15	APPLICATION RCVD DT	12-Aug-19	7
16	PHONE INT BY HR	15-Aug-19	3	3
17	FWD TO BUSINESS	17-Sep-19	33	36
18	RPLY RCVD FRM BSNESS	19-Sep-19	2	38
19	TEL INT BY BSNESS	25-Sep-19	6	44
20	FEEDBCK FRM BSNESS	27-Sep-19	2	46
21	F2F INT BY BSNESS
22	FEEDBCK FRM BSNESS
23	HIRE/REJECTED DT
24	OFFER SENT TO CANDT
25	OFFER ACPT BY CANDT
26	VISA INITITATE DT
27	VISA ISSUE DATE
28	SENT TO CANDT
29	TKT ISSUE DT
Sheet1

Cell Formulas
Range	Formula
E15:E29	E15	=IF(OR(D14="",D15=""),"",D15-D14)
F16:F29	F16	=IF(OR(D15="",D16=""),"",D16-$D$15)
D21	D21	=IFERROR(IF(ROWS(D$21:D21)>$B$1,"",INDEX(DataEntry!$W$4:$W$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$W$4:$W$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$21:D21)))),"")
D22	D22	=IFERROR(IF(ROWS(D$22:D22)>$B$1,"",INDEX(DataEntry!$X$4:$X$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$X$4:$X$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$22:D22)))),"")
D23	D23	=IFERROR(IF(ROWS(D$23:D23)>$B$1,"",INDEX(DataEntry!$Z$4:$Z$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$Z$4:$Z$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$23:D23)))),"")
D24	D24	=IFERROR(IF(ROWS(D$24:D24)>$B$1,"",INDEX(DataEntry!$AC$4:$AC$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$AC$4:$AC$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$24:D24)))),"")
D25	D25	=IFERROR(IF(ROWS(D$25:D25)>$B$1,"",INDEX(DataEntry!$AE$4:$AE$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$AE$4:$AE$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$25:D25)))),"")
D26	D26	=IFERROR(IF(ROWS(D$26:D26)>$B$1,"",INDEX(DataEntry!$AG$4:$AG$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$AG$4:$AG$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$26:D26)))),"")
D27	D27	=IFERROR(IF(ROWS(D$27:D27)>$B$1,"",INDEX(DataEntry!$AH$4:$AH$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$AH$4:$AH$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$27:D27)))),"")
D28	D28	=IFERROR(IF(ROWS(D$28:D28)>$B$1,"",INDEX(DataEntry!$AI$4:$AI$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$AI$4:$AI$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$28:D28)))),"")
D29	D29	=IFERROR(IF(ROWS(D$29:D29)>$B$1,"",INDEX(DataEntry!$AK$4:$AK$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$AK$4:$AK$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$29:D29)))),"")

 

[footoo]

You did not answer post #4. (Is the result TRUE or FALSE ?)

Do you have column D formatted to not show zeros?

 

[aliaslamy2k]

I am still getting the result. Please see below for your reference.

Recruitment Dashboard - Ali - 3 - Live V0.01.xlsm
	C	D	E	F
13	Process	Dates	Days	Days from App rcvd
14	ERF RECEIVED DATE	05-Aug-2019
15	APPLICATION RCVD DT	12-Aug-2019	7
16	PHONE INT BY HR	15-Aug-2019	3	3
17	FWD TO BUSINESS	17-Sep-2019	33	36
18	RPLY RCVD FRM BSNESS	19-Sep-2019	2	38
19	TEL INT BY BSNESS	25-Sep-2019	6	44
20	FEEDBCK FRM BSNESS	27-Sep-2019	2	46
21	F2F INT BY BSNESS		-43735	-43689
22	FEEDBCK FRM BSNESS		0	-43689
23	HIRE/REJECTED DT		0	-43689
24	OFFER SENT TO CANDT		0	-43689
25	OFFER ACPT BY CANDT		0	-43689
26	VISA INITITATE DT		0	-43689
27	VISA ISSUE DATE		0	-43689
28	SENT TO CANDT		0	-43689
29	TKT ISSUE DT		0	-43689
30	TKT SENT TO CANDT		0	-43689
31	DEP DATE		0	-43689
32	ARR DATE		0	-43689
33	FINGER PRNT DT		0	-43689
34	QID ISSUANCE DT		0	-43689
Report

 

[aliaslamy2k]

You did not answer post #4. (Is the result TRUE or FALSE ?)

Do you have column D formatted to not show zeros?

Below is the formatting i did in column D

format cells...-->Custom --> Type --> dd-mmm-yyyy;;

 

[sandy666]

I am still getting the result. Please see below for your reference.

I don't know what are you doing there
copy table from post #6

and paste into the proper place (probably C13)
I did nothing more than these two formulas and drag down

E15: =IF(OR(D14="",D15=""),"",D15-D14)
F16: =IF(OR(D15="",D16=""),"",D16-$D$15)