Oct 04 2021 02:54 AM
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 |
Oct 04 2021 02:57 AM
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.
Oct 04 2021 03:06 AM
@Sergei Baklan Yes, of course. Thank you so much!
Oct 04 2021 03:25 AM
SolutionThank 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.
Oct 04 2021 04:38 AM
Oct 05 2021 02:49 AM
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",""))))
Oct 05 2021 02:42 PM
That could be like
=IF( B8 = "", "Not paid",
IF( B8 = "X", "Absent",
IF( B8 <= $C$2,"Paid",
IF( B8 > $C$2,"Late payment", "") )) )
Oct 10 2021 06:48 PM
Oct 04 2021 03:25 AM
SolutionThank 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.