Changing cells based on dates

[ammdumas]

Two things I'm horrible with...dates and macro loops.

I'm trying to go down a list and update a field based on a date(past due to be specific). IE, If the date in Column J3 is equal or older than TODAY then I want to change the field in column N3 to "PAST DUE".

I want this to cycle through the speadsheet (J4,J5,J6,etc.) every time it is opened.

Help is greatly appreciated...

 

[Joe4]

No macro or loops are necessary. Simply use an IF statment in N3:

=IF(J3<=NOW(),"Past Due","")

 

[ammdumas]

Cool. It works. But I should have been a little more specific. There's some conditional formatting happening over that row when the field in question is 'PAST DUE'. The formula put 'PAST DUE' in even if there is no date in J3. How do I skip blanks?

 

[Joe4]

Simple modification:

=IF(AND(J3>0,J3<=NOW()),"Past Due","")

 

[ian watkins]

Try using another IF statement

=IF(J3<>"",IF(J3<=NOW(),"Past Due",""),"")

 

[ammdumas]

Exxxxxxcelent!

They both work. Thanks guys. Now all I need to do is send email notifications for past due items. I'm going to take 1st crack at it before I whine for help. Thanks again.