Convert ddmmyy to Qtr yy

dessim

New Member
Joined
Aug 4, 2019
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
sorry my mistake..i actually meant 25/12/2019 or 25/12/19
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
Lol. I looked up the documentation on the format function and ‘q’ was not in there. Microsoft lied to me!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top