Date Help

**Acems033** · 12-03-2009, 12:11 PM

I am working on a Excel Program to keep track of Personnel Training. I am trying to get a Condition set so if I enter a date, such as Drivers License Expiration Date, into F8 F9 F10 and so on for each Employee it will Change to Yellow when it is with-in 3 months of Expiring or turn Red when it has passed. I have tried a lot of tips online and every time it randomly changes colors when nothing is in the cell or a passed date does not change at all. How can I fix this?

**vikas.bhandari** · 12-03-2009, 12:34 PM

Please try the attached version.

I am assuming, that whenever you enter anything in f 8 and so on, you want to compare the entered date along with the current date. It can be easily compared with the conditional formatting.

logic:

Conditional formatting in F8 :

Condition1 : =AND(NOW()>F8,F8<>"")
Condition2 : =AND((MONTH(F8)-MONTH(NOW()))<3,F8<>"")

Copy the cell and paste the formatting in the cells below. I have also achieved this in the attached excel.

Thanks,
Vikas

**Acems033** · 12-03-2009, 01:03 PM

THANKS! That works perfectly!

Now when I highlight all the cells it pertains to and enter the formula it refers back to F8. How can I get it to refer only to the cell it needs to (such as G8 H8) and so on? Do I have to format each cell individually or do is their a shortcut? I know their probably is but my minds eye needs glasses today for some reason and I cant remember how.

Thanks a lot. You made my job 100% EASIER!

**Acems033** · 12-03-2009, 01:26 PM

Never Mind. I got it. THANKS a bunch. I really appreciate it.

**DonkeyOte** · 12-03-2009, 01:33 PM

Vikas

Condition2 : =AND((MONTH(F8)-MONTH(NOW()))<3,F8<>"")

I think you would want to adapt that to a DATEDIF function... using the above every single date would meet the above criteria in Dec for ex.

**vikas.bhandari** · 12-03-2009, 01:56 PM

Originally Posted by DonkeyOte

Vikas

I think you would want to adapt that to a DATEDIF function... using the above every single date would meet the above criteria in Dec for ex.

Ahh....pretty long I have worked on Excel. Now coming back slowly slowly :D

Thanks for that!!!
Vikas Bhandari

**Acems033** · 12-03-2009, 02:55 PM

Ok, It works for day and month but if I enter a date of 12/31/2012 for example it still says its with-in the 3 month date. It only looks at the day and month. How do I format it to look at year also?

**vikas.bhandari** · 12-04-2009, 05:31 AM

Oh, Sorry about that :

Please change it to following Conditions:

=AND(ISERROR(DATEDIF(NOW(),F8,"d")),F8<>"")
=AND(NOT(ISERROR(DATEDIF(NOW(),F8,"d"))),F8<>"",(DATEDIF(NOW(),F8,"m")<4))

HTH,
Vikas

**Acems033** · 12-21-2009, 04:16 AM

Ok, now I need to change the formula

=AND(ISERROR(DATEDIF(NOW(),F8,"d")),F8<>"")
=AND(NOT(ISERROR(DATEDIF(NOW(),F8,"d"))),F8<>"",(DATEDIF(NOW(),F8,"m")<4))

to only look at the month and year. As in 07/2013 and so on. We changed our files to only have the Month and Year something expires since 99% of our training expires on the last day of a certain month.