Formatting Excel Dates as quarters

Is there any way to format Excel Dates as quarters?  I thought I saw somewhere that you can use q as a formatting code in a custom format the way you do m, d and y, but it didn't work, it just showed q.  I have a user who is trying to format the date axis of her Excel custom chart (she got it off the internet and it is a Gantt chart) in quarters.

Her data looks like:

Category    Task    Start Date    End Date    Color

GEMs          Ruby   8/1/20         12/31/21    Blue

                   Jade    12/1/20       12/31/23    Blue

Colors        opaque 10/1/20      12/31/2022 Red

So instead of having actual m/d/yy's (8/1/20) showing on her date axis across the top of the Gantt chart, she wants it to show Q3-20 and Ruby's start date would begin under that and go through Q4-21.

Thanks!

Convert date to weekday, month, quarter or year in Excel?
https://www.extendoffice.com/documents/excel/1496-excel-convert-date-to-weekday-month-quarter-year.html
Says you enter a date in one cell, and it shows as 2012/12/21. Is there a way to show only the year, only the quarter, only the month or the weekday? The following methods can help you easily convert or format any kinds of date to display only the weekday name or month name or quarter name or year name in Microsoft Excel.
.

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi E_Kemper_strs,

Thank you for reaching out to us. I am Thuy, an Independent Advisor and Microsoft user like you.

For this task, you can insert 2 more columns to retrieve the "Q3-20" format for "Start Date" and "End Date" value, then modify the chart data using these newly created columns. You can refer to the formula below and apply it for all the date-value cells.

="Q"&ROUNDUP(MONTH(C2)/3,0)&"-"&MID(YEAR(C2),3,2)

in which cell C3 is "8/1/20" in the sample data you provided.

Hope this helps!

Regards

15 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks for your suggestion.  What I find is that this changes the dates from numbers to text and then it can't chart them.

Is there a numeric custom format for quarters?

Thanks,

Ellen

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I don't see a straight date format that automatically translates a date into quarters.  On the other hand, if you use PivotTables, then yes, there is a way to do that. Actually, sometimes the PivotTable will automagically generate quarters for you!

Group Dates in a Pivot Table
https://excelchamps.com/blog/group-dates-in-a-pivot-table/
A pivot table is full of awesome features. It doesn’t matter how big your data is, there is no problem for pivot table to handle it. Creating a group of dates in a pivot table is one those features. With this, you can instantly create insightful and ready to present reports. When you work with a data where you have dates in it, the best thing to summarize it in months, years, or quarters.
.  *  Groups Dates in a Pivot Table by Month / Quarter / Year / Second / Minute / Hour / day
.  *  Groups of 7 Days to Create Weekly Summary
.  *  Hourly Summary if You Have Time with Dates
.  *  Creating a Custom Date Range Summary
.  *  Grouping Two Different Fields
.  *  Un-Grouping
ET MR PivotTables.docx.

This is the best date format article I've found, no option for quarters

Format Dates and Times and Dates.

! Custom Date Formats in Excel
https://www.myexcelonline.com/blog/custom-date-formats-in-excel/
Custom date formats in Excel allow you to display only certain parts of the date.
Say you had a date of 18/02/1979, which coincides to be my birthday.
You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the month as a number on in abbreviated form and the year in two or four digits.
You cam also mix and match to create a custom date format or even enter a custom text that would show something like: Today is Sunday
You can download the following workbook which shows you the different formats that you can use and see the tutorial below of how this can be easily achieved.
DOWNLOAD WORKBOOK
CODE OUTPUT      DATE/TIME  APPEARS AS
m                 Displays the month as a number                                18/02/1979   2
mm              Displays the month as a number with leading zeros          18/02/1979   02
mmm            Displays the month in abbreviated form                      18/02/1979   Feb
mmmm         Displays the month in full form                                   18/02/1979   February
mmmmm      Displays the first letter of the month                           18/02/1979   F
d                  Displays the day as a number                                    18/02/1979   18
dd                Displays the day as a number with leading zeros          01/02/1979   01
ddd               Displays the day in abbreviated form                          18/02/1979   Thu
dddd             Displays the day in full form                                       18/02/1979   Thursday
yy                 Displays the last two digits of the year                        18/02/1979   79
yyyy             Displays all the digits of the year                                18/02/1979   1979
mmmm d, yyyy   Displays the month, the date and the year                  18/02/1979   February 18, 1979
mmmm-yyyy Displays the month-year                                            18/02/1979   February-1979
“Today is” dddd   Displays a custom text for the Today function             11/06/2015   Today is Thursday
h                  Displays the hour as a number                                   9:05:13          9
hh                Displays the hour as a number with leading zeros       9:05:13          09
m                 Displays minute as number                              9:05:13         5
mm              Displays minutes as a number               9:05:13         05
s                  Displays seconds as a number               9:05:03         3
ss                 Displays seconds as a number               9:05:13         03
ss.s              Display tenth of second                         9:05:13.3      13.3
ss.00            Displays hundredths of second               9:05:13.03    13.03
AM/PM         Displays the hour indicating AM or PM    9:55:13         AM
https://www.myexcelonline.com/wp-content/uploads/2015/06/Number-Formats-Dates.gif
.

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi E_Kemper_strs,

As the value of "quarters" always begins with letter "Q" then it could not be a numeric value. Also, you could definitely plot a chart in which x axis labels are in text format. You can refer to the link below for more information on how to change axis labels in a chart.

https://support.microsoft.com/en-gb/office/chan...

Regards

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks!  I will check this out!

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks - I will check this out.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Please let me know if you need more assistance.
Regards

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 31, 2024 Views 45,305 Applies to: