SOLVED

If then formula with dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2808352%22%20slang%3D%22en-US%22%3EIf%20then%20formula%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2808352%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I%20need%20your%20help%20with%20my%20formula.%20I%20want%20to%20create%20a%20sheet%20on%20monthly%20payment%20collection%20with%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(I14%26lt%3B%22C2%22%2C%22Paid%22%2CIF(I14%26gt%3B%22C2%20%22%2C%22Late%20Payment%22%2CIF(I14%3D%22%22%2C%22Not%20paid%22%2C%22Not%20paid%22)))%3C%2FP%3E%3CP%3Ewhere%20C2%20I%20write%20down%20the%20desired%20date.%20In%20I14%20I%20would%20write%20down%20the%20payment%20date.%20If%20there%20is%20no%20payment%20then%20the%20cell%20will%20be%20blank%20and%20I%20want%20to%20comes%20up%20with%20%22Not%20paid%22%20but%20still%20it%20comes%20up%20with%20%22Paid%22%3C%2FP%3E%3CP%3EI%20have%20another%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(I14%26lt%3B%3DDATEVALUE(%2210%2F09%2F2021%22)%2C%22Paid%22%2CIF(I14%26gt%3BDATEVALUE(%2210%2F09%2F2021%22)%2C%22Late%20payment%22%2CIF(ISBLANK(I14)%2C%22%22%2C%22Not%20Blank%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20it%20still%20comes%20up%20with%20the%20same%20thing.%20Can%20anyone%20help%20me%20on%20this%20matter%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22404px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3ENO%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3EPaid%20on%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EMode%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3EStatus%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3E27-Aug%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EIBG%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3EPaid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3E9-Sep%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EIBG%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3EPaid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3E10-Sep%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EIBG%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3EPaid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3E22-Sep%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EIBG%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3ELate%20payment%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3E4-Aug%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EIBG%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3EPaid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.875px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%22157.406px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278.8646px%22%3EIBG%3C%2FTD%3E%3CTD%20width%3D%22102.854px%22%3EPaid%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2808352%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2808380%22%20slang%3D%22en-US%22%3ERe%3A%20If%20then%20formula%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2808380%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174142%22%20target%3D%22_blank%22%3E%40SyahiraH3199%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20possible%20to%20share%20the%20same%20within%20Excel%20file%3F%20It%20looks%20like%20you%20have%20texts%20which%20looks%20like%20dates%2C%20not%20dates%20which%20are%20actually%20numbers%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2808416%22%20slang%3D%22en-US%22%3ERe%3A%20If%20then%20formula%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2808416%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BYes%2C%20of%20course.%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2808469%22%20slang%3D%22en-US%22%3ERe%3A%20If%20then%20formula%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2808469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174142%22%20target%3D%22_blank%22%3E%40SyahiraH3199%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20I'd%20change%20formula%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20B8%3D%22%22%2C%20%22Not%20paid%22%2C%20IF(B8%20%26lt%3B%3D%24C%242%2C%22Paid%22%2C%20IF(%20B8%26gt%3B%24C%242%2C%22Late%20payment%22%2C%20%22%22)%20))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20original%20formula%26nbsp%3B%3DIF(%3CSTRONG%3EB8%26lt%3B%3D%24C%242%3C%2FSTRONG%3E%2C%22Paid%22%2CIF(B8%26gt%3B%24C%242%2C%22Late%20payment%22%2CIF(B8%3D%22%22%2C%22Not%20paid%22%2C%22%22)))%20first%20condition%20always%20works%20for%20any%20blank%20cell%20since%20it's%20always%20less%20than%20any%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2808634%22%20slang%3D%22en-US%22%3ERe%3A%20If%20then%20formula%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2808634%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much.%20I%20really%20appreciate%20it.%20You're%20my%20saviour!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2808660%22%20slang%3D%22en-US%22%3ERe%3A%20If%20then%20formula%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2808660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174142%22%20target%3D%22_blank%22%3E%40SyahiraH3199%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

 

NOPaid onModeStatus
227-AugIBGPaid
39-SepIBGPaid
410-SepIBGPaid
522-SepIBGLate payment
64-AugIBGPaid
7 IBGPaid

 

 

9 Replies

@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.

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

best response confirmed by SyahiraH3199 (Occasional Contributor)
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.

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

@SyahiraH3199 , you are welcome

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

@SyahiraH3199 

That could be like

=IF( B8 = "", "Not paid",
 IF( B8 = "X", "Absent",
 IF( B8 <= $C$2,"Paid",
 IF( B8 >  $C$2,"Late payment", "") )) )
I've tried and it works. Thank you so much! I really appreciate it!

@SyahiraH3199 , glad to help