Calculate using blanks

[Andrew Watson]

I have a report which I download from a database and I determine if a supplier is late with their delivery or not. If we update the vendors 'promise date' the original 'promise date' is transferred to the 'promise original' column. Currently when determining if a vendor is late I use an IF formula to determine if they are 'LATE' or 'ON TIME' using the differences in the 'Close Date' and 'Promise Date' Columns. I would now like to initially look at the 'Promise Original' and calculate off this date versus 'Close Date' in the first instance if they are late or not. If the cell in 'Promise Original' is blank then I would like to calculate from the 'Close Date' and 'Promise Date' Columns. I've tried using a few variations of an IF formula and also adding in an OR as well but I keep getting errors.

Can anyone help with his calculation?

Book1
	B	C	D	E
1	CLOSE DATE	PROMISE DATE	PROMISE ORIGINAL	ON TIME
2	19/07/2021	9/07/2021	6/07/2021	LATE
3	19/07/2021	9/07/2021		LATE
4	9/07/2021	9/07/2021		ON TIME
5	8/07/2021	9/07/2021		ON TIME
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IF(B2<=C2,"ON TIME",IF(B2>C2,"LATE"))

 

[maabadi]

How about:
View My formula at Column F:

Book1(AutoRecovered).xlsm
	A	B	C	D	E	F	G
1		CLOSE DATE	PROMISE DATE	PROMISE ORIGINAL	ON TIME
2		7/19/2021	7/9/2021	7/6/2021	LATE	Late
3		7/19/2021	7/9/2021		LATE	Late
4		7/9/2021	7/9/2021		ON TIME	ON TIME
5		7/8/2021	7/9/2021		ON TIME	ON TIME
6
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=IF(B2<=C2,"ON TIME",IF(B2>C2,"LATE"))
F2:F5	F2	=IF(D2="",IF(B2<=C2,"ON TIME","Late"),IF(B2<=D2,"ON TIME","Late"))

 

[Andrew Watson]

How about:
View My formula at Column F:

Book1(AutoRecovered).xlsm
	A	B	C	D	E	F	G
1		CLOSE DATE	PROMISE DATE	PROMISE ORIGINAL	ON TIME
2		7/19/2021	7/9/2021	7/6/2021	LATE	Late
3		7/19/2021	7/9/2021		LATE	Late
4		7/9/2021	7/9/2021		ON TIME	ON TIME
5		7/8/2021	7/9/2021		ON TIME	ON TIME
6
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=IF(B2<=C2,"ON TIME",IF(B2>C2,"LATE"))
F2:F5	F2	=IF(D2="",IF(B2<=C2,"ON TIME","Late"),IF(B2<=D2,"ON TIME","Late"))

Hi maabadi,

That worked great thank you very much. I did forget one other cell where a calculation is completed. It needs to calculate the the days. Currently using a very simple formula and I've tried adapting your response to this cell but my knowledge isn't that great to figure it our without asking. It is the same as before. If the value is blank it calculates the days late between cells B2 & C2 but if there is a value in cell D2 it would then calculate using B2 & D2.

Any thoughts?

Book1
	B	C	D	E	F	G	H	I	J	K
1	CLOSE DATE	PROMISE DATE	PROMISE ORIGINAL	ON TIME					DAYS LATE
2	19/07/2021	9/07/2021	6/07/2021	LATE	Late				10
3	19/07/2021	9/07/2021		LATE	Late				10
4	9/07/2021	9/07/2021		ON TIME	ON TIME				0
5	8/07/2021	9/07/2021		ON TIME	ON TIME				-1
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IF(B2<=C2,"ON TIME",IF(B2>C2,"LATE"))
F2:F5	F2	=IF(D2="",IF(B2<=C2,"ON TIME","Late"),IF(B2<=D2,"ON TIME","Late"))
J2:J5	J2	=B2-C2

 

[maabadi]

Yes. Try this at J2 and fill down:

=IF(D2="",B2-C2, B2-D2)

 

[Andrew Watson]

Yes. Try this at J2 and fill down:

=IF(D2="",B2-C2, B2-D2)

Hi maabadi,

It worked.

Thank you so much for your help. It is most appreciated.

 

[maabadi]

You're Welcome & Thanks for Follow-up.

 

[Peter_SSs]

In relation to the original question, I would assume that if a second promise date exists it is always after the original promise date?
If that is the case then you could use the column E formula below.
If my assumption is not correct, then you could use the column F formula, still considerably shorter than the earlier formula.

21 09 01.xlsm
	A	B	C	D	E	F
1		CLOSE DATE	PROMISE DATE	PROMISE ORIGINAL	ON TIME
2		19-Jul-21	09-Jul-21	07-Jun-21	LATE	LATE
3		19-Jul-21	09-Jul-21		LATE	LATE
4		09-Jul-21	09-Jul-21		ON TIME	ON TIME
5		08-Jul-21	09-Jul-21		ON TIME	ON TIME
On Time Late

Cell Formulas
Range	Formula
E2:E5	E2	=IF(B2<=MIN(C2,D2),"ON TIME","LATE")
F2:F5	F2	=IF(B2<=IF(D2="",C2,D2),"ON TIME","LATE")