how do I write a better formula?

samlee1981

New Member
Joined
Feb 12, 2019
Messages
7
I have the following If formula...

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(D5-15<P$3,"OVERDUE","NOT DUE")=IF(J5=isnumber,I5,"")

How do I write this correctly?
The first part works but the second nested IF is not correct.

D Column is course date, I Column is todays date minus 15 days (set to remind me that payment needs to have been made), J Column is payment received. P3 is todays date.

I want to say - if J5 contains a number, (e.g the date payment was received), make I5 blank. At the moment it just says overdue.


Any ideas how to reword it?

Many thanks
[/FONT]
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
If you are using < or > in your formula, put a space either side when posting it on the forum, otherwise it will cut it off at the < >
 

samlee1981

New Member
Joined
Feb 12, 2019
Messages
7
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(D5-15<P$3,"OVERDUE","NOT DUE")=IF(J5=isnumber, I5="")[/FONT]
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,178
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

for some reason, I can't copy whole of if formula over to show you!

instead of

=if(B1>5, "Yes","No")

put extra spaces like this

=if(B1 > 5, "Yes","No")
 

samlee1981

New Member
Joined
Feb 12, 2019
Messages
7
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF ( D5-15 < P$3, " OVERDUE", " NOT DUE")= IF ( J5 = isnumber, I5 ="" )[/FONT]
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

Maybe

=if(isnumber(J5),"",IF (D5-15 < P$3,"OVERDUE","NOT DUE"))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top