Overdue dates based upon formula driven result in another cell

[Detectiveclem]

Hi,

I currently use the following formula which works well and produces the text either "Due" or "Overdue" if no payment has been entered into that cell for the current Month. If payment is not received anytime between the first and last day of the month "Due" appears, this changes to "Overdue" if payment is not recorded when the next/later months are reached. Conditional formatting shows "Due" in Orange and "Overdue" in red. All good so far.


=IF(MONTH(TODAY())>MONTH($P$5),"Overdue",IF(MONTH(TODAY())=MONTH($P$5),"Due",""))

However I would like to add the number of days an from which payment was due or overdue, thus instead of saying simply "Due it would for example say Due 10 days ago, or "Overdue 35 days".


Any help would be appreciated.

 

[Detectiveclem]

Sorry I should have added that cell P5 contains the date ie 01/05/18 (1st May 2018) the next column at cell R5 the following month and so forth...

 

[WaterGypsy]

Something like

=IF(MONTH(TODAY())>MONTH($P$5),"Overdue "&TODAY()-EOMONTH($P$5)&" days",IF(MONTH(TODAY())=MONTH($P$5),"Due "&TODAY()-$P$5&" days ago",""))

 

[Detectiveclem]

Thank you Watergypsy, I will try this later and let you know how I get on.

 

[Detectiveclem]

Hi WaterGypsy,

This didn't work.

As it returned "to many arguments". When I removed the ) before &" days" this resulted in False.

 

[Detectiveclem]

Do you or anyone else have any other ideas I could try?

 

[WaterGypsy]

Sorry ..... EOMONTH($P$5) should say EOMONTH($P$5,0)

 

[Detectiveclem]

Hi WaterGypsy, thank you, all working fine now. Thank you once again for solving my problem