Contextures

Contextures News 20200519

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.

Sort Fiscal Months

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:

=CHOOSE(MONTH(C2),10,11,12,1,2,3,4,5,6,7,8,9)

If you add those fiscal months to a pivot table, they're numbers, and sort correctly, smallest to largest.

pivot table fiscal year

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")

pivot table fiscal year

When that new column is added to the pivot table, it sorts the fiscal months correctly.

pivot table fiscal year

There are more pivot table fiscal year examples on my Contextures site.

Fractions

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:

  • Apply the "Fraction" number format, that's on the Home tab of the Excel Ribbon.
  • Use the TEXT function. For example, combine text, and a decimal number in cell A5, with this formula:
    • ="Hours worked: " & TEXT(A5,"# ?/?")

Another trick is to change the fraction's slash to a colon, to show ratio

  • =SUBSTITUTE(TEXT(B4/C4,"#/######"),"/",":")

See more text formatting examples on my website, and see other ways to calculate a ratio in Excel.

Excel Articles

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?

Also see: My Excel Products || Previous Issues

Flowers and Penguins

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!

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

Calculate the fiscal year

text formatting examples

pivot table fiscal year

calculate a ratio in Excel

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 19, 2020 9:11 AM