month & year comparison
Posted by Marco on January 31, 2002 6:24 PM
I want to compare the month and year of one cell to others scattered around the worksheet. The first cell, A1, is populated with the TODAY() function. The second cell, B13, is formatted to display only month and year, Mar-02. If the month and year match, I want a conditional format to highlight only those cells that match.
I have worked on this for a few evenings and I do appreciate any help I can get. Thanks in advance!
Posted by Dan Aragon on January 31, 2002 7:07 PM
Someone else might be able to use a better way, but this works. Make sure that the Analysis ToolPak is added (Tools, Add Ins..., put a check mark next to Analysis Toolpak). Put this formula in another cell say C13 (you can hide the column from your sheet if necessary):
=IF(EOMONTH($A$1,0)=EOMONTH(B13,0),1,0)
Which puts a 1 if the months are the same, else 0.
Then you can use this formula in your conditional format in B13:
=C13=1
For some reason you can't put the EOMONTH formula directly in the conditional format, I'm guessing probably because it is an add-in formula.
Posted by marco on January 31, 2002 9:32 PM
When I go and click to add the Analysis ToolPak, it requires to load it from the CD. But for some reason it doesn't find a file it needs on the CD, data1.msi. I am back to square one.
Posted by Derek on January 31, 2002 10:16 PM
This worked for me.
Highlight the entire worksheet, then put this as a formula in conditional formatting
=AND(MONTH(A1)=MONTH($A$1),YEAR(A1)=YEAR($A$1))
Derek
Posted by Dan Aragon on February 01, 2002 10:41 AM
data1.msi is on all MS Office installation cd's, it just may be hard to find. You will need to search the CD to find where the file is located (Start -> Find -> Files or Folders. Change the drive letter to your CD Rom drive letter and search in all directories for the filename). You will probably want to take the time do this because the Analysis ToolPak is very useful.
