Forum Discussion
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 |
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.
9 Replies
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.
SergeiBaklan Yes, of course. Thank you so much!
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.
