CODEWITHSUNDEEP
excelexcel-formula
Michael
Michael

Reputation: 14218

Excel date format - quarter & year

Is it possible to change the cell format to display quarter and year q yyyy instead of dd-mm-yyyy?

Upvotes: 6

Views: 45191

Answers (6)

Austin
Austin

Reputation: 8565

For 1/7/2020 in cell A1,

=TEXT(A1,"yyyy")&"Q"&ROUNDUP(MONTH(A2)/3,0)

Produces: "2020Q1"

Upvotes: 0

Tomas Watz
Tomas Watz

Reputation: 71

=ROUNDUP(MONTH(A1)/3,0)&TEXT(A1," yyyy")

This is enough if you just need the number of the quarter

Upvotes: 5

Elizabeth
Elizabeth

Reputation: 11

Try this formula:

="Q"&INT((MONTH(AZ2)+2)/3)&"-"&YEAR(AZ2)

This will take 12/31/2016 in cell AZ2 and change it to Q4-2016

Upvotes: 1

brettdj
brettdj

Reputation: 55672

No - quarters are not a customisable format

The closest I think you could get would be to use a formula which has two potential limitations

  1. in place of the current value
  2. which would then no longer be a date

=INDEX({"Mar","Jun","Sep","Dec"},ROUNDUP(MONTH(A1)/3,0))&TEXT(A1," yyyy")

will return the quarter (based on a calendar year) and year of a date in A1, i.e. for today's date Dec 2011

Upvotes: 5

Michael
Michael

Reputation: 11

=IF([@Date]="","N/A",TEXT([@Date],"YYYY-Q")&ROUNDUP(MONTH([@Date])/3,0))

=IF(A1="","N/A",TEXT(A1,"YYYY-Q")&ROUNDUP(MONTH(A1)/3,0)

e.g., 6/15/2015 will become 2015-Q3

Just in case the date field is blank this formula will fill with N/A instead of getting 1900-Q1

Upvotes: 1

Dheer
Dheer

Reputation: 4066

It is not possible to have the said format.

Upvotes: 1

Related Questions