 SOLVED

# If then formula with dates

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

9 Replies

# Re: If then formula with dates

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.

# Re: If then formula with dates

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

best response confirmed by SyahiraH3199 (Occasional Contributor)
Solution

# Re: If then formula with dates

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.

# Re: If then formula with dates

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

# Re: If then formula with dates

@SyahiraH3199 , you are welcome

# Re: If then formula with dates

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",""))))

# Re: If then formula with dates

That could be like

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

# Re: If then formula with dates

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