# EOMonth Function

EOMONTH function isn't working properly

Current Formula in the Criteria table:

=AND('Portfolio Summary'!F8<>"",AND('Portfolio Summary'!F8<NOW(),'Portfolio Summary'!F8<>"N/A"))

Date cells are formatted as "mmm-yy" on the portfolio summary sheet

Problem:  The current formula is picking up records that are not past due (for example, typing Apr-10 returns date 01/04/2010 in the formula bar, and then it is counted as being past due because the current date is April 23, 2010.  How can I write the formula to flag dates as past due only if they are before the current month.

I tried changing the above formula using the following:

=AND('Portfolio Summary'!F8<>"",AND(EOMONTH('Portfolio Summary'!I2,-1),'Portfolio Summary'!F8<>"N/A"))

Where \$I\$2 = today()
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Use this formula...
=AND(OR('Portfolio Summary'!F8<>"",'Portfolio Summary'!F8<>"N/A"),'Portfolio Summary'!F8>EOMONTH('Portfolio Summary'!I2,-1))
Saurabh...
0
VBA DeveloperCommented:
Try this:
=AND(OR('Portfolio Summary'!F8<>"",'Portfolio Summary'!F8<>"N/A"),'Portfolio Summary'!F8<=EOMONTH(TODAY(),-1))

Based off of today's date:
For 4/1/2010 it will return false
For 3/31/2010 and older it will return true
0
Author Commented:
Thank you, I will test this out and let you know the result.
0
Commented:
why not take out the OR
=AND(ISNUMBER('Portfolio Summary'!F8),'Portfolio Summary'!F8<=EOMONTH(TODAY(),-1))
0
Commented:
Just realised that it still wont capture the N/A error so try this
IF(ISNUMBER('Portfolio Summary'!F8),'Portfolio Summary'!F8<=EOMONTH(TODAY(),-1),FALSE)
0

Experts Exchange Solution brought to you by