Month and free text

[rhmkrmi]

How can I autofill cells with MMM-YY and a free text?
For example, COSTS OCT-18 and then drag this to the next cells to get COSTS NOV-18, etc.

Thank you!

 

[Rick Rothstein]

How can I autofill cells with MMM-YY and a free text?
For example, COSTS OCT-18 and then drag this to the next cells to get COSTS NOV-18, etc.!

Something like this should work...

="COSTS "&UPPER(TEXT(DATE(2018,ROWS($1:10),1),"mmm-yy"))

where you would set the red highlighted number to the month number you want to start with.

 

[rhmkrmi]

Great, thanks a lot.
How can I use COLUMNS instead of ROWS because I need them as column headers.
Thanks again.

 

[Peter_SSs]

Another option, not using formulas (though the month abbreviations will not be upper case):
Put Oct-18 and Nov-18 in the first 2 cells, select those 2 cells and drag the Fill Handle (the little black square at the bottom right of the selection) across as far as you need.
With all the cells still selected, custom format (Ctrl+1) the cells as "COSTS " mmm-yy

 

[Peter_SSs]

For a formula method, dragged to the right:
=UPPER(TEXT(EDATE("1-Oct-2018",COLUMNS($B1:B1)-1),"CO\ST\S mmm-yy"))
.. where the red part is your first date and the blue part is the cell address of the first formula cell.

 

[rhmkrmi]

Thanks, this is a great tip but with this solution, I have to enter the dates in each column manually after custom formatting.

 

[rhmkrmi]

This works fine, thanks a lot.

What are the slashes in CO\ST\S?

 

[Peter_SSs]

.. but with this solution, I have to enter the dates in each column manually after custom formatting.

The dates could be entered by formula (from a given starting cell date), but in any case perhaps the formula suggestion in post 5 suits you better?

 

[Peter_SSs]

This works fine, thanks a lot.

Cheers.

What are the slashes in CO\ST\S?

In a date (& time) format, S stands for seconds, The slash stops Excel from interpreting them as seconds and ensures an S is actually used.
Just remove the slashes from one of those formulas and look at the result.

 

[rhmkrmi]

Fantastic, thanks for your help and the excel lesson!