Formula Friday #1

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

To display the calendar month of a date use the TEXT Function

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, with this formula:

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

Much easier!

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

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

Take the formula to the next level

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

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

Either 

=TEXT([reference cell],”YY”

or 

=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

Ax

Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.

Have a project in mind?

I can help bring your ideas to life. Let’s talk about what we can build together.

More like this