• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for Passed Upcoming and Paid dates

Sharon Slatter

Sharon Slatter

New Member
Good Morning all,

I have the below Formula which is great but I want to add to it to show, upcoming due dates (3 days before due) saying "Becoming Due" and also if a date is in Column Q then the cell that the formula is in (Column M) will say "Paid"

=IF(ISBLANK(L13),"",IF(L13<TODAY(),"Overdue","Not Due"))

Can anyone show me how to adapt to incorporate these please.

Thanks
Shazz xx
 
Try:

=IF(ISBLANK(L13),"",IF(Q13<>"","Paid",IF(L13<TODAY(),"Overdue",IF(TODAY()>=L13-3,"Becoming Due","Not Due"))))
 
That's great, is there any way it can be adapted so that it will still show Paid even if Column L is blank but Column Q has a date in it?
 
You can switch first 2 arguments...

=IF(Q13<>"","Paid",IF(ISBLANK(L13),"",IF(L13<TODAY(),"Overdue",IF(TODAY()>=L13-3,"Becoming Due","Not Due"))))
 
That worked creat, I just want to ask something else of you, is there also a way of adapting the code so that if the word "Rejected" or "Credit" shows in Column Q instead of a date, can these show in Column L instead as well??
 
I can't reply without you obiding by the rules first and posting where you have crossposted with links to them....
 
Great, thanks.

Try:

=IF(ISTEXT(Q13),Q13,IF(Q13<>"","Paid",IF(ISBLANK(L13),"",IF(L13<TODAY(),"Overdue",IF(TODAY()>=L13-3,"Becoming Due","Not Due")))))
 
Sharon Slatter
You wrote: Sorry, I wasn't aware that posting on two forums was cross posting.
Have You read next link?

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
There are explain many rules.

And also that other forum has clear rules too...
Have You read those?
 
NBVC,

Try:

=IF(ISTEXT(Q13),Q13,IF(Q13<>"","Paid",IF(ISBLANK(L13),"",IF(L13<TODAY(),"Overdue",IF(TODAY()>=L13-3,"Becoming Due","Not Due")))))

That one does not work unfortunately, and it doesn't have the Credit/Rejected text in it. I wont waste any more of your time. Thank you so much for your help.
 
Sharon Slatter
It's good to refresh and follow those too.
There are reasons, why those rules has done.
... and ...
if You could upload a sample file, it would make many things smoother.
 
Hiya,

Here is an example sheet, as you can see were it says Credit or Rejected, it is showing as paid, I want the word Paid to be replaced by Credit or Rejected.
 

Attachments

  • Example.xlsm
    38.3 KB · Views: 4
This is the formula to put in M3:

=IF(ISTEXT(Q3),Q3,IF(Q3<>"","Paid",IF(ISBLANK(L3),"",IF(L3<TODAY(),"Overdue",IF(TODAY()>=L3-3,"Becoming Due","Not Due"))))) copied down
 
Hi Vletm and NBVC,

Unfortunately neither of these do what I need them to do, the first part with the Due, Paid Not Due Yet works fine, but it does not incorporate the Rejected and Credited parts into the formula. See attached.
 

Attachments

  • Example.xlsm
    38.2 KB · Views: 3
Sharon Slatter
You sent 'same file' back ...
You should add there at least double amount of rows which covers EVERY Your needed rule!
eg 'Rejected' at least TWO rows, which could give ONLY CORRECT results.
 
You must log in or register to reply here.
Back
Top