Due date and past due formula help please

[nub9l]

I am tasked with making a spread sheet showing the years, months, and days until a specific date. and then if its past due show the same but as a negative or some value to show it is past due.
what i am using now is:
=IF(DATEDIF(S4,N4,"y")=0,"",IF(DATEDIF(S4,N4,"y")=1,DATEDIF(S4,N4,"y")&"year",DATEDIF(S4,N4,"y")&"years"))&IF(AND(DATEDIF(S4,N4,"y")<>0,DATEDIF(S4,N4,"ym")<>0),", ","")&IF(DATEDIF(S4,N4,"ym")=0,"",IF(DATEDIF(S4,N4,"ym")=1,DATEDIF(S4,N4,"ym")&"month",DATEDIF(S4,N4,"ym")&" months"))&IF(AND(OR(DATEDIF(S4,N4,"y")<>0,DATEDIF(S4,N4,"ym")<>0),DATEDIF(S4,N4,"md")<>0),", ","")&IF(DATEDIF(S4,N4,"md")=0,"",IF(DATEDIF(S4,N4,"md")=1,DATEDIF(S4,N4,"md")&" day",DATEDIF(S4,N4,"md")&" days"))

N4 is the objects due date and s4 is todays date. the problem I am having is when something is past due as in n4 is in the past. I am getting a number error. is there a way to eight get the years monts and days as a negative or some other value to show its in the past while still showing the time until one is due. Ive been racking my brain for a while now and im lost. thank you all

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FDibbins]

Agree a sample WB would help, but this is what I use to determine yrs/mths/days between 2 dates...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

Now, yo ID those dates past due, you could use Conditional Formatting to make the cell change color when that even happens?

[nub9l]

heres a sample

[leelnich]

Hi nub91- You need to format the result as text, and structure it like this:

=IF($N$4=$S$4,"Due TODAY!",IF($N$4>$S$4,[Calculated Difference1]&" Remaining.",[Calculated Difference2]&" PAST DUE!"))

where Calculated Difference is a place-keeper for all that code everybody else is writing.

Since you're dealing w dates, N4 and S4 should be integers. Note that Calculated Difference1 uses N4-S4, while Calculated Difference2 uses S4-N4.
If you want to HIGHLIGHT cells that are Past Due, use this Conditional Formatting Formula (in F2 and copy): =RIGHT(F2,4) = "DUE!"

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[avk]

In "F2"

Please Login or Register  to view this content.

copy down.