# Thread: Convert ddmmyy to Qtr yy Thanks: 0 Likes: 0

1. ## Convert ddmmyy to Qtr yy

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.

2. ## Re: Convert ddmmyy to Qtr yy

Is '25122019' stored as text?

3. ## Re: Convert ddmmyy to Qtr yy

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

4. ## Re: Convert ddmmyy to Qtr yy

Originally Posted by dessim
sorry my mistake..i actually meant 25/12/2019 or 25/12/19
Maybe this...

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

5. ## Re: Convert ddmmyy to Qtr yy

Here's a UDF.

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

6. ## Re: Convert ddmmyy to Qtr yy

Originally Posted by lrobbo314
Here's a UDF.

Code:
```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)...
Code:
```Function QUARTER(D As Date) As String
QUARTER = "Qtr" & Format(D, "q yy")
End Function```

7. ## Re: Convert ddmmyy to Qtr yy

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

8. ## Re: Convert ddmmyy to Qtr yy

Originally Posted by lrobbo314
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.

9. ## Re: Convert ddmmyy to Qtr yy

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.

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

10. ## Re: Convert ddmmyy to Qtr yy

Originally Posted by 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.

Code:
```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...
Code:
```Function QUARTER(d As Date) As String
QUARTER = Format(d, "\Q\t\rq yy")
End Function```