End of Month convertion

[BjornBjorg]

Hello! Small question..

I work in retail and I need to keep track of dates for products that we sell in the supermarket. I want to do that in an excel file and have a neat overview when something goes out of date and if that's anytime soon or not. I want to implement that with formulas (between =TODAY() and =TODAY()+30 lights up red, between =TODAY()+30 and =TODAY()+60 lights up yellow and more of that). 

Now when I enter dates, some products that I enter only show the month and year instead of the exact date. When I enter that in excel: let's say 11-2020, it shows up as nov-20, but in the formula bar, it shows as 1-11-20. Now, of course, a product that says nov-20 can be sold throughout the whole of November, but with the formulas and the dates, that creates a little confusion, as for the month October at this point, there is no red colour anymore, because it has been converted to 1-10-2020, instead of 31-10-2020. The fun part in this is: I can manually change it in the formula bar to 31-10-2020 and then it gives the red colour again and it still shows up as oct-20, but to do that for hundreds of products manually, is a bit of a reach.

The question: Is there any possible way that - if you enter month+year in the space - the date automatically converts to the end of the month in the formula bar, so that there is less confusion with the colour scheme and it gets a better overview, or do I need to manually change everything and keep track of it that way?

Thank you in advance!

Bjorn

[#2]

@BjornBjorg 

There are many ready-made templates, why create one when there are so many and good ones already?

If I may recommend you, take a pre-made template that could fit and modify it to suit your project.

Download free, pre-built templates
Excel for Microsoft 365 Word for Microsoft 365 PowerPoint for Microsoft 365 Publisher for Microsoft 365 Access for Microsoft 365 Visio Professional 2016 Visio 2013 Visio 2010 Excel 2019 Word 2019 PowerPoint 2019 Publisher 2019 Access 2019 Project Professional 2019 Excel 2016 Word 2016 PowerPoint 2016 Publisher 2016 Access 2016 InfoPath 2013 Project Professional 2016 Excel 2013 Word 2013 PowerPoint 2013 Publisher 2013 Access 2013 Excel 2010 Word 2010 PowerPoint 2010 Access 2010 Publisher 2010 Project 2010 InfoPath 2010 Excel 2007 Word 2007 Project Online Desktop Client Project Standard 2016 Project Standard 2019 Less

https://support.microsoft.com/en-us/office/download-free-pre-built-templates-29f2a18d-29a6-4a07-998b...

Here are two examples from Microsoft's template reservoir.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@BjornBjorg Add another column and enter (assuming the first date is in A1):

=EOMONTH(A1,0)

or, in Dutch:

=LAATSTE.DAG(A1;0)

 and copy it down as far as needed. Apply conditional formatting to this new column.