Formula Friday #01
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
