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]

Hi, Cyrildrd,

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

Copied down...
In your example you placed the formula until row 21 only. Row 22 is still your old formula...

mrxcel2020.xlsx
	C	D	E	F
13	Process	Dates	Days	Days from App rcvd
14	ERF RECEIVED DATE	08/05/2019
15	APPLICATION RCVD DT	08/12/2019	7
16	PHONE INT BY HR	08/15/2019	3	3
17	FWD TO BUSINESS	09/17/2019	33	36
18	RPLY RCVD FRM BSNESS	09/19/2019	2	38
19	TEL INT BY BSNESS	09/25/2019	6	44
20	FEEDBCK FRM BSNESS	09/27/2019	2	46
21	F2F INT BY BSNESS
aliaslamy2k

Cell Formulas
Range	Formula
E14:E21	E14	=IFERROR(D14-D13,"")
F16:F21	F16	=IFERROR(D16-$D$15,"")
D21	D21	=IFERROR(IF(ROWS(D$20:D20)>$B$1,"",INDEX(DataEntry!$U$4:$U$5000,SMALL(IF((DataEntry!$H$4:$H$5000=$D$5),ROW(DataEntry!$U$4:$U$5000)-ROW(DataEntry!$H$4)+1),ROWS(D$20:D20)))),"")

 

[footoo]

Below is the formatting i did in column D

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

OK. The formula in column D can evaluate to 0, and if so the 0 is not shown because of the formatting.
Try this :

=IF(OR(D20="",D20=0,D21="",D21=0),"",D21-D20)

 

[sandy666]

btw. formatting is the LAST thing what you should do, after checking everything is working properly

 

[aliaslamy2k]

OK. The formula in column D can evaluate to 0, and if so the 0 is not shown because of the formatting.
Try this :

=IF(OR(D20="",D20=0,D21="",D21=0),"",D21-D20)

Hi Footoo,

This formula works absolutely fine. Below is the results

Thank you so much

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-Jul-2019
15	APPLICATION RCVD DT	10-Jul-2019	5
16	PHONE INT BY HR	13-Jul-2019	3	3
17	FWD TO BUSINESS	15-Jun-2019	-28	-25
18	RPLY RCVD FRM BSNESS	17-Jun-2019	2	-23
19	TEL INT BY BSNESS	22-Jul-2019	35	12
20	FEEDBCK FRM BSNESS	23-Jul-2019	1	13
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
30	TKT SENT TO CANDT
31	DEP DATE
32	ARR DATE
33	FINGER PRNT DT
34	QID ISSUANCE DT
Report