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!
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!
This should do. But you should have actual dates:
=YEAR(A1)&"-"&ROUNDUP(MONTH(A1)/3,0)&"Q"
In B2 then drag down
=YEAR(A2)&"-"&IF(MONTH(A2)<4,4,1+INT((MONTH(A2)-4)/3))&"Q"
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Base on Op's post:
=YEAR(A2)&"-"&IF(MONTH(A2)<3,4,1+INT((MONTH(A2)-3)/3))&"Q"
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.
Or try:
=YEAR(EDATE(A1,-2))&"-"&CEILING(MONTH(EDATE(A1,-2))/3,1)&"Q"
Here is another way
Formula:
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
If A1 is the text, try this:
=YEAR(EDATE(A1&"/1",-2))&"-"&CEILING(MONTH(EDATE(A1&"/1",-2))/3,1)&"Q"
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks