We help IT Professionals succeed at work.

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

Hi,

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

Thanks.
Comment
Watch Question

Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
lets say your date is in A1  then put ="Q"&ROUNDUP(MONTH(A1)/3,0)
Bill PrewTest your restores, not your backups...
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

Author

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).
Professor JMicrosoft 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
Test your restores, not your backups...
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

Author

Commented:
Thank you, but that did not work. Converting time zone
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
please try this ="Q"&ROUNDUP(MONTH(L5)/3,0)&"-"&YEAR(INT(L5))

Author

Commented:
This works. Thanks, Bill! Converting time zone
Professor JMicrosoft 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))))
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Great.


»bp

Author

Commented:
Thanks, Professor JimJam. This works as well. Converting time zone
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome.

Author

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

Commented:
:)