Due dates conundrum

[John.F]

I have a spreadsheet with a date field in column D,
If the date value in column D is equal to TODAY(), I need to display "Due Today" in Column F and leave column G blank or display "0"
If the date value in column D is before TODAY(). I need to display "Overdue" in Column F and VAL(number of days overdue) in column G
If the date value in column D is after TODAY(), I need to display "Due In" in column F and VAL(number of days until due date) in Column G.

I can do the number of days overdue, but I get #name errors for a value of TODAY() or future date.

 

[ganeshpoojary05]

Try the below formula:

Formula for F Column:
=IF(D2=TODAY(),"Due Today",IF(D2<TODAY(),"Overdue","Due In"))


Formula for G Column:
=IF(F2="Overdue","Overdue by "&TODAY()-D2&" days",IF(F2="Due In","Due in by "&D2-TODAY()&" days",""))

 

[John.F]

Thanks for your help. the formula for Column F seems incomplete - or were you giving me more respect than I'm due by assuming I could figure the rest out ?

John

 

[ganeshpoojary05]

Try the below formula:

Formula for F Column:
=IF(D2=TODAY(),"Due Today",IF(D2<TODAY(),"Overdue","Due In"))


Formula for G Column:
=IF(F2="Overdue","Overdue by "&TODAY()-D2&" days",IF(F2="Due In","Due in by "&D2-TODAY()&" days",""))

 

[Jon von der Heyden]

Hi

Formula in F2:
=LOOKUP(G2,{-9.99E+307,0,1},{"Due In","Due Today","Overdue"})

Formula in G2:
=TODAY()-D2

Format G2 as:
0;0;""

 

[ganeshpoojary05]

Please ignore the previous thread. here is the complete formula
Try the below formula:

Formula for F Column:
=IF(D2=TODAY(),"Due Today",IF(D2<TODAY(),"Overdue","Due In"))



Formula for G Column:
=IF(F2="Overdue","Overdue by "&TODAY()-D2&" days",IF(F2="Due In","Due in by "&D2-TODAY()&" days",""))
<!-- / message -->

 

[Jon von der Heyden]

Please ignore the previous thread. here is the complete formula
Try the below formula:

Formula for F Column:
=IF(D2=TODAY(),"Due Today",IF(D2<TODAY(),"Overdue","Due In"))



Formula for G Column:
=IF(F2="Overdue","Overdue by "&TODAY()-D2&" days",IF(F2="Due In","Due in by "&D2-TODAY()&" days",""))
<!-- / message -->

Try using a space before and after greater than / less than characters.

 

[ganeshpoojary05]

I guess i have some problem in pasting the formula:

Formula for F column
=if(D2=today(),"Due Today",if(d2<today(),"Overdue","Due In"))

 

[John.F]

Try using a space before and after greater than / less than characters.

It worked without spaces. But it looks better with!

 

[John.F]

I guess i have some problem in pasting the formula:

Formula for F column
=if(D2=today(),"Due Today",if(d2<today(),"Overdue","Due In"))

Iguess you do! However, I did figure out how to complete the formula - and it works.

Thanks a lot.