We help IT Professionals succeed at work.

# How do I convert  date/time stamps  to quarters (Q1, Q2, etc...)?

on
Hi,

How can I convert 08/18/17 04:31PM EDT to quarters (Q1, Q2, etc...)?

Thanks.
Comment
Watch Question

## View Solutions Only

Microsoft Excel Expert
Top Expert 2014

Commented:
lets say your date is in A1  then put ="Q"&ROUNDUP(MONTH(A1)/3,0)
Expert of the Year 2019
Top Expert 2016

Commented:
If you have a date field in A2 then try these approaches.  The first just gives the quarter with a "Q", the other includes the year in a slightly different format.  Adjust as needed.

=CONCATENATE("Q",ROUNDUP(MONTH(A2)/3,0))

=CONCATENATE(ROUNDUP(MONTH(A2)/3,0),"Q",YEAR(A2))

»bp

Commented:
Thank you. Both solutions suggested work well when I remove the time zone. Unfortunately the javascript export is in this format and I would like a one step conversion solution, but I'm fine with 2 steps if the first one removes the timezone (EDT).
Microsoft Excel Expert
Top Expert 2014

Commented:
if you want to remove time from it then first step should be  =TEXT(INT(A1),"MM/DD/YY")  this removes the time
Expert of the Year 2019
Top Expert 2016
Commented:
Give these a try, assuming that text string is what's in the A2 cell.

=CONCATENATE("Q",ROUNDUP(MONTH(DATEVALUE(LEFT(A2,FIND(" ",A2))))/3,0))

=CONCATENATE(ROUNDUP(MONTH(DATEVALUE(LEFT(A2,FIND(" ",A2))))/3,0),"Q",YEAR(DATEVALUE(LEFT(A2,FIND(" ",A2)))))

»bp

Commented:
Thank you, but that did not work.
Microsoft Excel Expert
Top Expert 2014

Commented:

Commented:
This works. Thanks, Bill!
Microsoft Excel Expert
Top Expert 2014
Commented:
please try this . it will show year as well.

="Q"&ROUNDUP(MONTH(DATEVALUE(LEFT(L6,LEN(L6)-6)))/3,0)&"-"&YEAR(INT(DATEVALUE(LEFT(L6,LEN(L6)-6))))
Expert of the Year 2019
Top Expert 2016

Commented:
Great.

»bp

Commented:
Thanks, Professor JimJam. This works as well.
Microsoft Excel Expert
Top Expert 2014

Commented:
you are welcome.

Commented:
Sorry. Wrong screenshot, Professor JimJam. Here's the correct one.
Microsoft Excel Expert
Top Expert 2014

Commented:
:)