Converting 6 different date formats into Quarters

[amarbalani]

I currently havedates (calendar year) that are in 6 different formats and want to convert allthe different formats into quarters, within one formula

Format	Example	Expected output (Quarter)	Notes
DD/MM/YYYY	12/01/2017	4Q 2017
MM/YYYY	12/2017	4Q 2017
XH YYYY	1H 2017 2H 2017	1Q 2017 3Q 2017	Want anything with "1H YYYY" to be 1Q YYYY Want anything with "2H YYYY" to be 3Q YYYYY
YYYY	2017	1Q 2017	If standalone year default to 1Q YYYY
XQ YYYY	4Q 2017	4Q 2017
YYYY-YYYY	2022	1Q 2018	IF a range of years , default to 1Q of the first year in range

<tbody>
</tbody>

Actual data from Column D in worksheet:

11/26/2018
12/15/2018
12/27/2018
4Q 2018
4Q 2018
2H 2018
2H 2018
2018
2018
2018
2018
2018
2018
2018
2018-2019
2018-2019
2018-2019
2018-2019
2018-2019
2018-2019
2018-2019
2018-2019
2018-2020
2018-2020
2018-2021
2018-2022
01/01/2019
01/03/2019
01/22/2019
02/17/2019
02/18/2019
02/27/2019
02/2019
02/2019
02/2019
03/04/2019

<tbody>
</tbody>

Is there a singleformula that can be used to take the different "formats" from thetable above and can be used to convert to the "Expected Output(Quarter)", as outlined in the table above ?

Thank you

 

[ranman256]

ALL formats are using the number....the date. (the display,format, is differnt)
Paste this code into a module and produce the Quarter into a cell:
usage: cvtDate2Qtr(a3)

Public Function cvtDate2Qtr(ByVal pvDate)
Dim sQtr As String

If IsNull Then Exit Function
Select Case month(pvDate)

    Case 1, 2, 3
       sQtr = "1Q:"
    Case 4, 5, 6
       sQtr = "2Q:"
    Case 7, 8, 9
       sQtr = "3Q:"
    Case 10, 11, 12
       sQtr = "4Q:"
End Select
cvtDate2Qtr = sQtr & Year(pvDate)
End Function

 

[inactiveuserps07]

@amarbalani, if all of the different date data in Column D is presently formatted as text, then you can use the following formula in, say, E2 and copied down (assuming your data starts in D2):

=IF(IFERROR(SEARCH("Q",D2),-1)>0,D2,IF(IFERROR(SEARCH("H",D2),-1)>0,CHOOSE(VALUE(LEFT(D2,1)),1,3)&"Q "&RIGHT(D2,4),IF(OR(LEN(TRIM(D2))=4,IFERROR(SEARCH("-",D2),-1)>0),"1Q "&LEFT(D2,4),ROUNDUP(VALUE(LEFT(D2,FIND("/",D2)-1))/3,0)&"Q "&RIGHT(D2,4))))

If your data in Column D is in mixed formats, then use this slightly longer formula instead:

=IF(IFERROR(SEARCH("Q",D2),-1)>0,D2,IF(IFERROR(SEARCH("H",D2),-1)>0,CHOOSE(VALUE(LEFT(D2,1)),1,3)&"Q "&RIGHT(D2,4),IF(OR(LEN(TRIM(D2))=4,IFERROR(SEARCH("-",D2),-1)>0),"1Q "&LEFT(D2,4),ROUNDUP(VALUE(LEFT(IF(ISNUMBER(D2),MONTH(D2)&"/"&YEAR(D2),D2),FIND("/",IF(ISNUMBER(D2),MONTH(D2)&"/"&YEAR(D2),D2))-1))/3,0)&"Q "&RIGHT(IF(ISNUMBER(D2),MONTH(D2)&"/"&YEAR(D2),D2),4))))