If then formula with dates

[SyahiraH3199]

Hi all, I need your help with my formula. I want to create a sheet on monthly payment collection with this formula:

=IF(I14<"C2","Paid",IF(I14>"C2 ","Late Payment",IF(I14="","Not paid","Not paid")))

where C2 I write down the desired date. In I14 I would write down the payment date. If there is no payment then the cell will be blank and I want to comes up with "Not paid" but still it comes up with "Paid"

I have another formula: 

=IF(I14<=DATEVALUE("10/09/2021"),"Paid",IF(I14>DATEVALUE("10/09/2021"),"Late payment",IF(ISBLANK(I14),"","Not Blank")))

and it still comes up with the same thing. Can anyone help me on this matter?

NO	Paid on	Mode	Status
2	27-Aug	IBG	Paid
3	9-Sep	IBG	Paid
4	10-Sep	IBG	Paid
5	22-Sep	IBG	Late payment
6	4-Aug	IBG	Paid
7		IBG	Paid

[#2]

@SyahiraH3199 

Is it possible to share the same within Excel file? It looks like you have texts which looks like dates, not dates which are actually numbers in Excel.

[#3]

@Sergei Baklan Yes, of course. Thank you so much!

[#4 · Accepted solution]

@SyahiraH3199 

Thank you. I'd change formula as

=IF( B8="", "Not paid", IF(B8 <=$C$2,"Paid", IF( B8>$C$2,"Late payment", "") ))

In original formula =IF(B8<=$C$2,"Paid",IF(B8>$C$2,"Late payment",IF(B8="","Not paid",""))) first condition always works for any blank cell since it's always less than any date.

[#5]

Thank you so much. I really appreciate it. You're my saviour!

[#6]

@SyahiraH3199 , you are welcome

[#7]

Hi @Sergei Baklan, let say if I want to add =IF(J12="X","Absent"), is it possible? where should I put it?

=IF(J12="","Not paid",IF(J12<=$C$3,"Paid",IF(J12>$C$3,"Late payment",""))))

[#8]

@SyahiraH3199 

That could be like

=IF( B8 = "", "Not paid",
 IF( B8 = "X", "Absent",
 IF( B8 <= $C$2,"Paid",
 IF( B8 >  $C$2,"Late payment", "") )) )

[#9]

I've tried and it works. Thank you so much! I really appreciate it!

[#10]

@SyahiraH3199 , glad to help