Excel Fractions and Fiscal Years
May 19, 2020
Showing fractions, sorting months in a fiscal year, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
You'll get my next newsletter on June 2nd -- we're starting the summer schedule now, with a newsletter every 2 weeks.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Does your business have a fiscal year that starts in a month other than January? You can use formulas to calculate the fiscal year, quarter or month.
For example, if the fiscal year starts in April, this formula would return the fiscal month number for a date in cell C2:
If you add those fiscal months to a pivot table, they're numbers, and sort correctly, smallest to largest.
Someone asked me how to show both the fiscal month number and the calendar month name, to make things clearer.
This formula combines the 2-digit fiscal month number ( @FM), and the 3-digit month name for the OrderDate.
=TEXT([@FM],"00") & TEXT([@OrderDate]," mmm")
When that new column is added to the pivot table, it sorts the fiscal months correctly.
There are more pivot table fiscal year examples on my Contextures site.
Do you ever use fractions in Excel? I was working with recipe quantities last week, and formatted the amounts with fractions (1 1/2), instead of decimals (1.5)
Here are a couple of ways to format numbers as fractions:
Another trick is to change the fraction's slash to a colon, to show ratio
Here are a few Excel-related articles that you might find useful or interesting.
Excel Tips -- Watch Excelling in Excel, a mini course from the University of Edinburgh. There 3 videos - data organization, tips and tricks, data visualisation.. (Level - Intermediate)
Dashboard: Excel MVP, Ingeborg Hawighorst, built an interactive dashboard that shows the development of the Covid-19 infection in New Zealand. She started with Excel and Power Query, then switched to Power BI. Learn how she built the dashboard, and you can access the live dashboard at this link. (Level - Int/Adv)
Excel Humour: See what people said about Excel recently. Have you ever accidentally included the zip code in an Excel sum?
The public schools are still closed, so I'm enjoying online "classes" with my grandkids. My grandson is learning math and video production, and having fun with both topics. My granddaughter and I do art sessions. Last week we did 3 basic origami designs - flowers, leaves and penguins.
Origami is a difficult thing to teach and learn over a FaceTime connection, but with patience and tenacity, we did it! Here's my granddaughter, showing off her completed projects. Usually, penguins wear a tuxedo, but this is his brightly-coloured summer outfit!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200519ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 8, 2020 2:44 PM