mmm-yy Date format for end of month?

[dropkickweasel]

I'm working on a workbook that is used to track the inventory and expiry dates of products.
As new stock is added to the workbook, the expiry dates are formatted as "mmm-yy".
I believe this is treated by Excel as being the first of the specified month.
However, all of the products go out of date at the end of each month.

Is there any way to change the way Excel perceives "mmm-yy" so that it is treated as the last day of each month, rather than the first?

The intended next step of this process is to apply conditional formatting to show when products are within X months of their expiry date, will expire at the end of the current month, or have expired.

Thanks in advance for any contributions before I start trying to improvise.

 

[bebo021999]

If date "mmm-yy" in cell A1, and real date, formatted as "mmm-yy"
A2 is end month date:

=EOMONTH(A1,0)

If A1 is text:
A2:

=EOMONTH(DATEVALUE("1-"&A3),0)

 

[dropkickweasel]

Hi Bebo021999,

Thank you for your response.

That definitely works, but is there a way to do this without adding an additional column to my table?

Essentially, rather than using a formula to convert "mmm-yy" to the last day of the month, is there a setting I can change where it is automatically considered the last day of the month when entered in the "mmm-yy" format?

 

[Fluff]

How are the dates being entered?

 

[dropkickweasel]

How are the dates being entered?

Manually, by writing "Jan-22" (for example).

 

[Fluff]

In that case why not just the day as well?

 

[dropkickweasel]

In that case why not just the day as well?

A very valid question!

Were I the end user, I would be happy with that solution and may well suggest this to the end user, but I wanted to explore other options first.

Assuming that the end user is not prepared to take on the additional load of inputting dd-mmm-yy, do you know of any way for Excel to recognise mmm-yy as the last day of the month rather than the first?

 

[Fluff]

You can't, if you type Jan-22 into a cell xl will automatically add the day as 1 & that cannot be changed.
But if this is just for conditional formatting use can use the formula that bebo021999 suggested in your CF rule

 

[RoryA]

You'd need code to convert the dates, or you could just change your conditional formatting to assume the date is the end of the specified month using EOMONTH.

 

[dropkickweasel]

Thank you to Fluff and RoryA for making the same suggestion.

That's the conclusion I was drawing too, but I always think it's worth asking if there's alternate ways to do a thing that I don't know about yet.

Many thanks.