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?
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)
Saurabh Singh TeotiaCommented:
Use this formula...
=AND(OR('Portfolio Summary'!F8<>"",'Portfolio Summary'!F8<>"N/A"),'Portfolio Summary'!F8>EOMONTH('Portfolio Summary'!I2,-1))
TracyVBA 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
Olga_BianchiAuthor Commented:
Thank you, I will test this out and let you know the result.  
why not take out the OR
=AND(ISNUMBER('Portfolio Summary'!F8),'Portfolio Summary'!F8<=EOMONTH(TODAY(),-1))
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.