Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Convert ddmmyy to Qtr yy

    Is '25122019' stored as text?
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert ddmmyy to Qtr yy

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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,227
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Convert ddmmyy to Qtr yy

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

    =1+INT((MONTH(A1)-1)/3)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,227
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Convert ddmmyy to Qtr yy

    Quote Originally Posted by lrobbo314 View Post
    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 by Rick Rothstein; Aug 4th, 2019 at 02:55 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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!
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,227
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Convert ddmmyy to Qtr yy

    Quote Originally Posted by lrobbo314 View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,227
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Convert ddmmyy to Qtr yy

    Quote Originally Posted by lrobbo314 View Post
    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 by Rick Rothstein; Aug 4th, 2019 at 04:51 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •