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
[TABLE="width: 1"]
<tbody>[TR]
[TD] Format[/TD]
[TD] Example[/TD]
[TD] Expected output (Quarter) [/TD]
[TD] Notes[/TD]
[/TR]
[TR]
[TD] DD/MM/YYYY[/TD]
[TD] 12/01/2017[/TD]
[TD] 4Q 2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] MM/YYYY[/TD]
[TD] 12/2017[/TD]
[TD] 4Q 2017 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] XH YYYY[/TD]
[TD] 1H 2017

2H 2017 [/TD]
[TD] 1Q 2017

3Q 2017 [/TD]
[TD]

• • Want anything with "1H YYYY" to be 1Q YYYY
  • Want anything with "2H YYYY" to be 3Q YYYYY
  
  

[/TD]
[/TR]
[TR]
[TD] YYYY[/TD]
[TD] 2017[/TD]
[TD] 1Q 2017 [/TD]
[TD] If standalone year default to 1Q YYYY[/TD]
[/TR]
[TR]
[TD] XQ YYYY[/TD]
[TD] 4Q 2017[/TD]
[TD] 4Q 2017 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] YYYY-YYYY[/TD]
[TD]

• 1. 2022

[/TD]
[TD] 1Q 2018 [/TD]
[TD] IF a range of years , default to 1Q of the first year in range[/TD]
[/TR]
</tbody>[/TABLE]

Actual data from Column D in worksheet:
[TABLE="width: 214"]
<tbody>[TR]
[TD]11/26/2018[/TD]
[/TR]
[TR]
[TD]12/15/2018[/TD]
[/TR]
[TR]
[TD]12/27/2018[/TD]
[/TR]
[TR]
[TD]4Q 2018[/TD]
[/TR]
[TR]
[TD]4Q 2018[/TD]
[/TR]
[TR]
[TD]2H 2018[/TD]
[/TR]
[TR]
[TD]2H 2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2019[/TD]
[/TR]
[TR]
[TD]2018-2020[/TD]
[/TR]
[TR]
[TD]2018-2020[/TD]
[/TR]
[TR]
[TD]2018-2021[/TD]
[/TR]
[TR]
[TD]2018-2022[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[/TR]
[TR]
[TD]01/03/2019[/TD]
[/TR]
[TR]
[TD]01/22/2019[/TD]
[/TR]
[TR]
[TD]02/17/2019[/TD]
[/TR]
[TR]
[TD]02/18/2019[/TD]
[/TR]
[TR]
[TD]02/27/2019[/TD]
[/TR]
[TR]
[TD]02/2019[/TD]
[/TR]
[TR]
[TD]02/2019[/TD]
[/TR]
[TR]
[TD]02/2019[/TD]
[/TR]
[TR]
[TD]03/04/2019[/TD]
[/TR]
</tbody>[/TABLE]


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))))