Excel - Formula to Display Quarter and Year from a Date - Dueling Excel - Episode 1515

1,855 views · Published 10 February 2012 · 5:44 · Indexed 8 November 2026

Channel: MrExcel.com · 2012 · Education

Watch on YouTube

Microsoft Excel Tutorial: Convert date to quarter and year such as Q1-2025.

Welcome back to another exciting episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel, and as always, I'm joined by the amazing Mike "Excel Is Fun" Girvin. In this episode, we tackle a common question about formatting dates to show the corresponding quarter. Our goal is to display the quarter and year, such as Q4-2012, based on the given date. While there are various ways to approach this problem, we'll be using the CHOOSE and CEILING functions to make it happen.

First, I'll show you my solution using the CHOOSE function. We'll use the month of the date to determine which quarter it falls under, and then combine it with the "Q" and year using the ampersand symbol. This method works well if your fiscal year ends on December 31st. However, if your fiscal year ends on a different month, things can get a bit trickier. But don't worry, Mike has a solution for that too!

Mike's solution involves using the MONTH and CEILING functions to round up the month to the nearest multiple of 3. This gives us a number from 1 to 4, which we can then use to display the corresponding quarter. He also shares a handy shortcut for converting dates back to numbers using Ctrl+Shift+~. I was blown away by this tip and I'm sure you will be too!

But wait, there's more! Mike also shows us a simpler solution using the VLOOKUP function. This is a great option for beginners or those who prefer a more straightforward approach. Whichever method you choose, you'll be able to impress your colleagues with your newfound Excel skills.

I always learn something new from Mike during these dueling podcasts, and this episode was no exception. His use of the CEILING function and the Ctrl+Shift+~ shortcut were game-changers for me. I hope you found this episode as informative and entertaining as I did. Don't forget to tune in next week for another exciting dueling excel podcast from MrExcel and Excel Is Fun. See you then!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Quarters
(00:11) Question: How to show a quarter based on a date?
(00:21) Using CHOOSE and MOD functions
(01:08) Alternative solution using CEILING function
(02:53) Formatting the result
(03:19) Comparing the two solutions
(04:20) Learning from each other's techniques
(05:07) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
CEILING function in Excel
CHOOSE function in Excel
Converting dates to numbers in Excel
Dates in Excel
Excel podcast
Excel tips and tricks
Formatting dates in Excel
MOD function in Excel
MrExcel and Excel Is Fun collaboration
Quarter formula in Excel
VLOOKUP function in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153193/

We have a series of Dates. We want to show quarters for the year 2012. Bill 'MrExcel' Jelen goes Old School on this one while Mike 'ExcelisFun' Girvin goes for a more Functionally 'stream-lined' approach.
Mike and Bill look at the =CEILING, =MONTH, VLOOKUP functions and more.

Dueling Excel Podcast #97...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

More from this channel