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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920