+ Reply to Thread
Results 1 to 9 of 9

Convert year month to year quarter

  1. #1
    Registered User
    Join Date
    06-03-2016
    Location
    Malaysia
    MS-Off Ver
    MS365
    Posts
    60

    Convert year month to year quarter

    Hi, I would like to convert YYYY/MM to YYYY-quarter, wondering if any formula can do it. I have a long list in the datasheet.
    New Fiscal Year starts from March.

    yyyymm.jpg

    Appreciate if anyone can help. Thank you!

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Convert year month to year quarter

    This should do. But you should have actual dates:

    =YEAR(A1)&"-"&ROUNDUP(MONTH(A1)/3,0)&"Q"

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Convert year month to year quarter

    In B2 then drag down

    =YEAR(A2)&"-"&IF(MONTH(A2)<4,4,1+INT((MONTH(A2)-4)/3))&"Q"
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,832

    Re: Convert year month to year quarter

    Base on Op's post:

    =YEAR(A2)&"-"&IF(MONTH(A2)<3,4,1+INT((MONTH(A2)-3)/3))&"Q"

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,421

    Re: Convert year month to year quarter

    If the YYYY/MM values in column A are actually text values, then you can use this in B2:

    =IF(INT((RIGHT(A2,2)*1)/3)=0,LEFT(A2,4)-1&"-4Q",LEFT(A2,4)&"-"&INT((RIGHT(A2,2)*1)/3)&"Q")

    and copy down. See attached.

    Hope this helps.

    Pete

    P.S. I don't think the other solutions will produce the correct quarter for Jan or Feb 2019, as they all begin with YEAR(A2) and so will produce 2019-4Q.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,176

    Re: Convert year month to year quarter

    Or try:

    =YEAR(EDATE(A1,-2))&"-"&CEILING(MONTH(EDATE(A1,-2))/3,1)&"Q"

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Convert year month to year quarter

    Here is another way
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    2 2018/03 2018-1Q
    3 2018/04 2018-2Q
    4 2018/05 2018-2Q
    5 2018/06 2018-2Q
    6 2018/07 2018-3Q
    7 2018/08 2018-3Q
    8 2018/09 2018-3Q
    9 2018/10 2018-4Q
    10 2018/11 2018-4Q
    11 2018/12 2018-4Q
    12 2019/01 2019-1Q
    13 2019/02 2019-1Q
    14 2019/03 2019-1Q
    15 2019/04 2019-2Q
    16 2019/05 2019-2Q
    17 2019/06 2019-2Q
    18 2019/07 2019-3Q
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,176

    Re: Convert year month to year quarter

    If A1 is the text, try this:

    =YEAR(EDATE(A1&"/1",-2))&"-"&CEILING(MONTH(EDATE(A1&"/1",-2))/3,1)&"Q"

  9. #9
    Registered User
    Join Date
    06-03-2016
    Location
    Malaysia
    MS-Off Ver
    MS365
    Posts
    60

    Re: Convert year month to year quarter

    Sorry for didn't specify that my column A is in text format. i've tried all the formula given, and below are working well.
    =YEAR(EDATE(A1&"/1",-2))&"-"&CEILING(MONTH(EDATE(A1&"/1",-2))/3,1)&"Q"
    =IF(INT((RIGHT(A2,2)*1)/3)=0,LEFT(A2,4)-1&"-4Q",LEFT(A2,4)&"-"&INT((RIGHT(A2,2)*1)/3)&"Q")

    Thank you so much for all the guidance!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Splitting my data by month, year and quarter
    By heytherejem in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 03-14-2018, 03:25 PM
  2. How to sum data by month, by quarter, by year?
    By kevinSHR in forum Excel General
    Replies: 3
    Last Post: 05-06-2016, 04:06 PM
  3. [SOLVED] Month/quarter/semestre/year calculations
    By pccamara in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2016, 08:37 AM
  4. Replies: 4
    Last Post: 08-05-2015, 06:39 PM
  5. Need to convert Month/Date/Year to Year/Month/Date so excel will recognize
    By juliettelam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2014, 04:17 PM
  6. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  7. Expanding 4th quarter by month ONLY for certain year
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-28-2013, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1