How to display the calendar month of a date

Formula Friday #1

Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.

Which formula shows the month of year from a date?

Let’s say you are presented with a data set which shows the transaction date. But you need to provide a summary by month.

You could create a reference tab to list all the possible dates and then manually type in the months. Like this;

Or, you can let Excel do the work for you, by using this:

=TEXT([reference cell],”MMMM”)

Much easier!

If your want an abbreviated version of the month that looks like this

=TEXT([reference cell],”MMM”)

Take the formula to the next level

Now you’re comfortable extracting the month of a date. We can use the same logic to extract the year. 


=TEXT([reference cell],”YY”


=TEXT([reference cell],”YYYY”)

Now we can combine them to extract the month and year.

=TEXT([reference cell],”MMM”)&”-”&TEXT([reference cell],”YY”)

=TEXT([reference cell],“MMMM”)&”-“&TEXT([reference cell],“YYYY”)

One more thing

By using the TEXT Function. You are changing the format of the number from numerical to text. There is a really quick way to get around that by using the VALUE Function.

How did I do?

Hopefully, there have been a few cheers of achievement.

If it has left you wondering what else the TEXT function can do, to help with your data summaries. I have 3 additional formulas to share that you can start using right now.

Until next time


2 replies on “How to display the calendar month of a date”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.