cbbox that will dynamically show current year month and year

I have a combo that currently is populated from a table and I have in the cells:
Jan-2022
Feb-2022 and so on until Dec-2022

Is there a way to make it populate the Month & the current year. So next year the combo will show:

Jan-2023
Feb-2023 and so on until Dec-2023

This way the table stays the same length and I don't need to change the cells manually each year.

I've done searches but I cant find how to do it.

I can write the code in vba but don't want a macro in this spreadsheet.

I tried to concatenate 'Jan' & Year(now()) but couldn't get that to work

You can use this formula in cell K2:

=DATE(YEAR(TODAY()),ROWS($1:1),1)

and then apply a Custom Format to the cell of:

mmm-yyyy

Note that due to regional differences, you may need to use semicolons ( ; ) in the formula instead of commas ( , ), and that you may need to use a different format string if the Spanish words for month and year do not begin with m and y.

You can copy the formula down the table, to give you actual dates rather than text that looks like a date. If you prefer to have the text "dates" then you could use this formula instead:

=TEXT(DATE(YEAR(TODAY()),ROWS($1:1),1),"mmm-yyyy")

and the same comments about commas and format string still apply (though you would not have to set a Custom Format).

Hope this helps.

Pete

I wanted to keep the user away from the raw data, hence the combo and a dashboard that shows the filtered data.

Originally Posted by Pete_UK

You can use this formula in cell K2:

=DATE(YEAR(TODAY()),ROWS($1:1),1)

and then apply a Custom Format to the cell of:

mmm-yyyy

Note that due to regional differences, you may need to use semicolons ( ; ) in the formula instead of commas ( , ), and that you may need to use a different format string if the Spanish words for month and year do not begin with m and y.

You can copy the formula down the table, to give you actual dates rather than text that looks like a date. If you prefer to have the text "dates" then you could use this formula instead:

=TEXT(DATE(YEAR(TODAY()),ROWS($1:1),1),"mmm-yyyy")

and the same comments about commas and format string still apply (though you would not have to set a Custom Format).

Hope this helps.

Pete

I don't understand the rows part of your solution. I have to say it works very nicely but it has affected the filter search now.

Originally Posted by Pete_UK

You can use this formula in cell K2:

=DATE(YEAR(TODAY()),ROWS($1:1),1)

and then apply a Custom Format to the cell of:

mmm-yyyy

Note that due to regional differences, you may need to use semicolons ( ; ) in the formula instead of commas ( , ), and that you may need to use a different format string if the Spanish words for month and year do not begin with m and y.

You can copy the formula down the table, to give you actual dates rather than text that looks like a date. If you prefer to have the text "dates" then you could use this formula instead:

=TEXT(DATE(YEAR(TODAY()),ROWS($1:1),1),"mmm-yyyy")

and the same comments about commas and format string still apply (though you would not have to set a Custom Format).

Hope this helps.

Pete

I finally found the problem and it now works, I changed n1 to text function and the filter now works perfectly. How do I add your function as the solution and close the thread?

Originally Posted by pb21uk

... How do I add your function as the solution and close the thread? ...

Well, I think you have already discovered that, as the thread has been marked as SOLVED (using Thread Tools above your first post in this thread). Only Moderators and Admins can close a thread so no further posts can be added.

Thanks also for the rep.

Pete