Forcing month/year entry to last day of month

**Gunther Maplethorpe** · 06-10-2013, 12:28 PM

I use a certain portion of our workbooks to enter the due dates of certain equipment, which are always listed in month/year (e.g. 4/17), and which means that they are good until the last day of said month. That is, a due date of "5/16" is good through May 31, 2016.

I'd like for my users to be able to enter "5/16" and have that cell properly identify as May, 2016 (instead of May 16 of the current year)...more specifically, the last day of May, 2016. All of this so that I can conditionally format any cell where that date has past - that is, if my user enters "5/16" on May 15, 2016, it won't flag.

I've been using the following VBA (thanks to ChemistB, a couple years ago) to force the month/year part, but I can't make it go to last day. Any thoughts?

Please Login or Register  to view this content.

**Melvinrobb** · 06-10-2013, 01:08 PM

Would the changes below work?

Please Login or Register  to view this content.

**Gunther Maplethorpe** · 06-11-2013, 12:47 PM

It appears that there must be something interfering...when I use Melvinrobb's solution in a blank worksheet, it works fine. But when I try to add it to my worksheets, it fails...

Here's an example - I've stripped out everything else, but left the conditional and cell formatting in place. Can anyone explain what's going on?

**Melvinrobb** · 06-11-2013, 12:54 PM

In what cell would a user enter "16" and in what cell would a user enter "May 15, 2013"?
Where would the desired output appear, and when would it occur?
I'm just starting to learn Macros, so a brief breakdown of what the steps are would be helpful.

**Gunther Maplethorpe** · 06-11-2013, 01:02 PM

In the example sheet I attached above, cell F1 would have a "ful" date (eg 6/11/13), while cells D3:D12 would be entered month/year.

Conditional formatting would highlight any of those cells that are empty, or if the month/year is earlier than today's date (F1).

Also need the ability to NA the cells with the clickboxes...

**Melvinrobb** · 06-11-2013, 01:14 PM

I didn't realize you would want to replace a formula with a value. I also did not realize the macro worked automatically.
Anyways, the first couple times I opened the file it didn't seem to work, but it did seem to work the third time. The date in F1 is irrelevant though, is it not? It has no effect on the macro or date that will appear in column D

If you enter "June 14, 2013" into D3, it will become "June 31, 2014". That seems to work fine for me.

**Gunther Maplethorpe** · 06-11-2013, 01:47 PM

The date in F1 is only relevant for the conditional formatting, yes.

I'm also experiencing the sometimes it works, sometimes it doesn't issue. Very weird.

Perhaps more importantly, I can't delete an entry without it reverting to "Jan 2030"...any idea where that's coming from?

**Melvinrobb** · 06-11-2013, 02:22 PM

I understood the formula portion, and wanted to provide you with that in case that was the hang-up you were stuck on.
Unfortunately I am simply not educated enough in the Macro department to help you with what is occuring now. Sorry.

**Gunther Maplethorpe** · 06-13-2013, 03:16 PM

No worries. In truth, a macro is likely to be unacceptable in the long run, as these workbooks are accessed through a secondary software (Waters Vision Publisher) that frequently has issues with macros.

If anyone else comes across this thread that has a solution that is not macro-based, I look forward to it.