Over due and due dates in a payment table.

[Detectiveclem]

Hi,

Could anyone help me with a formula to allow me to show 'DUE' or 'OVER DUE' or leave the cell blank when niether apply.

I have a table showing displaying Months running along Row '5' and individuals names running down Column 'B'.


Each individual is expected to pay a certain amount each month. When they make payment the amount is recorded in the relevant cell.


I would like a formula, based on today's date which will show in the blank cells for the current month (i.e. where no monatery value has been entered) the word "DUE" but if payment exceeds 30 days this changes to "OVER DUE". But future months remain blank (until that month is reached).


Any help gratefully appreciated.

 

[jmacleary]

I have assumed you want it to operate for multiple years, so the cells in row 5 would have Jan 18, Feb 18, etc. in, so this is a cumbersome formula, but if you put this in cell c6 and copy across and down it should do the trick:

=IF(YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")>YEAR(C5)&TEXT(MONTH(C5),"00"),"Overdue",IF(YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")=YEAR(C5)&TEXT(MONTH(C5),"00"),"Due",""))

If you just want a single year this can be simplified to:

=IF(MONTH(TODAY())>MONTH(C5),"Overdue",IF(MONTH(TODAY())=MONTH(C5),"Due",""))

Regards
John

 

[Detectiveclem]

Thank you John, I used the single year formula and it works perfectly. Thank you once again.

Paul

 

[jmacleary]

Actually, the formula as I wrote it is slightly wrong - it should be: =IF(MONTH(TODAY())>MONTH(C$5),"Overdue",IF(MONTH(TODAY())=MONTH(C$5),"Due","")) - sorry!

 

[Detectiveclem]

Thank you I will change it, but it seemed to work. I appreciate you coming back to me. Kind regards, Paul