Convert ddmmyy to Qtr yy

[dessim]

Afternoon Ppl,

need some help here, how do i convert eg 25122019 (ddmmyyyy) or 251219 (ddmmyy) to show its Qtr4 19 ?

Any solution to this ?

Much thanks.

 

[lrobbo314]

Is '25122019' stored as text?

 

[dessim]

sorry my mistake..i actually meant 25/12/2019 or 25/12/19

 

[Rick Rothstein]

sorry my mistake..i actually meant 25/12/2019 or 25/12/19

Maybe this...

=1+INT((MONTH(A1)-1)/3)

 

[lrobbo314]

Here's a UDF.

Function QUARTER(dt As Date) As String
QUARTER = "Qtr" & WorksheetFunction.Floor((Month(dt) - 1) / 3, 1) + 1 & " " & Format(dt, "YY")
End Function

 

[Rick Rothstein]

Here's a UDF.

Function QUARTER(dt As Date) As String
QUARTER = "Qtr" & WorksheetFunction.Floor((Month(dt) - 1) / 3, 1) + 1 & " " & Format(dt, "YY")
End Function

There is a simpler UDF available (the Format function has an meta-character for quarter)...

Function QUARTER(D As Date) As String
  QUARTER = "Qtr" & Format(D, "q yy")
End Function

 

[lrobbo314]

Lol. I looked up the documentation on the format function and ‘q’ was not in there. Microsoft lied to me!

 

[Rick Rothstein]

Lol. I looked up the documentation on the format function and ‘q’ was not in there. Microsoft lied to me!

Using XL2010, when I click within the keyword Format within the VBA editor and press the F1 key, the help file for the Format function comes up... when I scroll down to the section titled "User-Defined Date/Time Formats (Format Function)", the "q" meta-character is listed about two-thirds of the way down.

 

[lrobbo314]

I see it now. Cool. Digging around in the docs i saw that you can use escape characters, didn't know that, so here's another way.

Function QUARTER(d As Date) As String
QUARTER = Format(d, "\Qtrq yy")
End Function

 

[Rick Rothstein]

I see it now. Cool. Digging around in the docs i saw that you can use escape characters, didn't know that, so here's another way.

Function QUARTER(d As Date) As String
QUARTER = Format(d, "\Qtrq yy")
End Function

I thought about doing it that way, but wasn't sure if the "t" or "r" might have meaning in some other locales (I have had this problem with date and time formats, so I am never sure if non-date formats also have this problem). Of course we could simply escape them as well I guess, just to be sure...

Function QUARTER(d As Date) As String
  QUARTER = Format(d, "\Q\t\rq yy")
End Function