Overdue dates for 2019

[Detectiveclem]

Hi I am currently using the following formula to successfully flag up when a person is either due to pay or if they haven't paid by the end of the month when payment was due. When less than 30days the cell shows "due x number of days ago". When over 30 days it shows 'Overdue and the number of days'.

=IF(MONTH(TODAY())>MONTH($C$4),"Overdue "&TODAY()-EOMONTH($C$4,-1)&" days",IF(MONTH(TODAY())=MONTH($C$4),"Due "&TODAY()-$C$4&" days ago",""))

This works perfectly, but now I am preparing the same spreadsheet for 2019. However all cells now show over due, even although we havent reached 2019 yet.

I need to incorporate the YEAR into the above formula but my attempts have proved useless so far. Can anyone help?

Thank you.

 

[taigovinda]

Hi,

I don't understand 100% what the texts are supposed to do... but does this give you something you can use? I don't see that you need to know the month, EOMONTH is actually returning an integer (as is TODAY) so you can just use those.

Book1
	A	B
1	Due Date	Answer
2	10/10/2018	Not in the right month anymore - 43 days overdue
3	11/01/2018	Still in the month - 21 days overdue
4	11/01/2017	Not in the right month anymore - 386 days overdue
Sheet3

Cell Formulas
Range	Formula
B2	=IF(TODAY()<=EOMONTH(A2,0),"Still in the month - ","Not in the right month anymore - ")&TODAY()-A2&" days overdue"

Tai

 

[Detectiveclem]

Hi Tai,

Thnk yo, I hae tried what you suggest, but doesn't work he way I would like. My current set up only populates the cells after the the start of the month, leaving future months blank.

Thanks anyway

 

[taigovinda]

Hi, I thought these were overdue accounts on the list. I still don't know exactly what you are looking for. Some sample dates and the expected output based on those dates would help. Here is the same function I offered before, wrapped wtih an if() statement in order to return a zero-length string if the due date is not yet passed:

=IF(TODAY()<=A2,"",<a2,"",< font=""></a2,"",<>IF(TODAY()<=EOMONTH(A2,0),"Still in the month - ","Not in the right month anymore - ")&TODAY()-A2&" days overdue")

Hope that helps!
Tai

 

[Fluff]

Is this what you're after

Excel 2013/2016
	C	D
4	22/11/2018	Due 0 days ago
5	22/10/2018	Overdue 53 days
6	21/10/2018	Overdue 53 days
7	01/09/2018	Overdue 83 days
8	01/09/2019
9	25/11/2019
Don't delete

Cell Formulas
Range	Formula
D4	=IF(TODAY()>EOMONTH($C4,0),"Overdue "&TODAY()-EOMONTH($C4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH($C4,0),"Due "&TODAY()-$C4&" days ago",""))

 

[Detectiveclem]

Hi again, sorry if I explain you might understand what I am trying to achieve. I have a spreadsheet with individuals named down Column B. This is then followed by column C to N headed up with each month. When an individual makes their monthly payment the amount is entered in the cell corresponding with they month they make payment. Future Months remain blank, however if no payment is entered in the current month by an individual text automatically appears showing for example “Due 21 days ago” (this is based on the 1st of the month in the column heading). If no payment is recorded when the date is greater than one month the text changes showing for example “Overdue 83 days”. Although using my formula works perfectly in the current year, when I set up the same spreadsheet for 2019 the cells were determining all the cells as overdue. For example January 2019 current shows “Overdue 39 days”, February 2019 shows “Overdue 70 days” and so forth with the number of days increasing each month. However December 2019 has the cells blank (as I want) so I have worked out that the formula purely looks at the month and does not take account of the YEAR.

I suspect when we reach the 1st January 2019 then everything will work normally, but in the meantime this is showing incorrect information as clearly the individuals payments are not Due or Overdue.

I hope I have explained this sufficiently as I think all that is need to include the current YEAR somewhere within my original formula. But I am open to any suggestions to get this to work perfectly, even for future years.

Thanks again

 

[Fluff]

Is this what you mean

Excel 2013/2016
	B	C	D	E	F	G	H
4		01/09/2018	01/10/2018	01/11/2018	01/12/2018	01/01/2019	01/02/2019
5	Bob	Overdue 83 days	Overdue 53 days	Due 21 days ago
6	Jim	100	100	Due 21 days ago
7	Gary	100	100	100
Don't delete

Cell Formulas
Range	Formula
C5	=IF(TODAY()>EOMONTH(C$4,0),"Overdue "&TODAY()-EOMONTH(C$4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH(C$4,0),"Due "&TODAY()-C$4&" days ago",""))
D5	=IF(TODAY()>EOMONTH(D$4,0),"Overdue "&TODAY()-EOMONTH(D$4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH(D$4,0),"Due "&TODAY()-D$4&" days ago",""))
E5	=IF(TODAY()>EOMONTH(E$4,0),"Overdue "&TODAY()-EOMONTH(E$4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH(E$4,0),"Due "&TODAY()-E$4&" days ago",""))
F5	=IF(TODAY()>EOMONTH(F$4,0),"Overdue "&TODAY()-EOMONTH(F$4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH(F$4,0),"Due "&TODAY()-F$4&" days ago",""))
G5	=IF(TODAY()>EOMONTH(G$4,0),"Overdue "&TODAY()-EOMONTH(G$4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH(G$4,0),"Due "&TODAY()-G$4&" days ago",""))
H5	=IF(TODAY()>EOMONTH(H$4,0),"Overdue "&TODAY()-EOMONTH(H$4,-1)&" days",IF(EOMONTH(TODAY(),0)=EOMONTH(H$4,0),"Due "&TODAY()-H$4&" days ago",""))

If not can you please post some data showing your layout

 

[Detectiveclem]

Hi Fluff,

Perfect, thank you very much. And thank you everyone else who came to my assistance.

 

[Fluff]

Glad we could help & thanks for the feedback