Test date to see if it is the end of a month

[Big.Moe]

***I'm using Excel 2003***

I have a particular cell in each worksheet (A1) that has the date of a transaction in dd/mm/yy format. In another cell (B1), I need to test the date in A1 to see if it is the end of a month and return which month it is. But, to complicate things, the dates in A1 are only workdays, so in theory the true end of month could fall on a weekend, but I need the prior Friday to show-up as end of month.

Thanks in advance.

[humdingaling]

Please Login or Register  to view this content.

if you cant get EOMONTH doesnt work
http://excel.tips.net/T002810_Using_..._Function.html

follow guide in link to get it working

[Big.Moe]

Thanks so much for that quick reply.

That worked perfectly for how I worded my question, but after seeing the results, I realized I worded my question slightly wrong. Your formula returns the end of month for all dates, what I need is to return the end of month only if the date being tested is in fact the last workday of that month. So for example, as I am nearing the end of month, the return from the formula would be "0", but when it hits the last workday then it would return the end of the month.

My apologies for the miscommunication.

[humdingaling]

uummm

so within 7 days of eomonth make the formula go ahead?

actually...would 5 be more correct?

[Big.Moe]

Let me use some real examples:

April
For April 1st through April 29th the formula would return a "0", but if A1 contained the date 4/30/15, then it would recognize that cell as the end of the month.

May
For May 1st through May 28th the formula would return a "0", but if A1 contained the date 5/29/15, then it would recognize that cell as the end of the month since it is the last workday of May (30 & 31 fall on a weekend).

I hope that clarifies things.

[Big.Moe]

I think I have part of the solution which tests for the last day of the month:

Please Login or Register  to view this content.

Now I just need help to test for the last workday of the month.

[Big.Moe]

Well, I surprised myself. I think this formula does the trick. Not sure if it is the most efficient, but it seems to work:

Please Login or Register  to view this content.

[humdingaling]

as long as it works for you

you could just replace that date bit with EOMONTH

Please Login or Register  to view this content.