• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 44
  • Last Modified:

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.
0
Jason Kyles
Asked:
Jason Kyles
  • 6
  • 5
  • 3
2 Solutions
 
ProfessorJimJamCommented:
lets say your date is in A1  then put ="Q"&ROUNDUP(MONTH(A1)/3,0)
0
 
Bill PrewCommented:
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
0
 
Jason KylesAuthor 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).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ProfessorJimJamCommented:
if you want to remove time from it then first step should be  =TEXT(INT(A1),"MM/DD/YY")  this removes the time
0
 
Bill PrewCommented:
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
0
 
Jason KylesAuthor Commented:
Thank you, but that did not work. Converting time zone
0
 
ProfessorJimJamCommented:
please try this ="Q"&ROUNDUP(MONTH(L5)/3,0)&"-"&YEAR(INT(L5))
0
 
Jason KylesAuthor Commented:
This works. Thanks, Bill! Converting time zone
0
 
ProfessorJimJamCommented:
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))))
0
 
Bill PrewCommented:
Great.


»bp
0
 
Jason KylesAuthor Commented:
Thanks, Professor JimJam. This works as well. Converting time zone
0
 
ProfessorJimJamCommented:
you are welcome.
0
 
Jason KylesAuthor Commented:
Sorry. Wrong screenshot, Professor JimJam. Here's the correct one. Capture.PNG
0
 
ProfessorJimJamCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now