Announcement

Collapse
No announcement yet.

CODE GOLF - December 2015 Answers

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • CODE GOLF - December 2015 Answers



    Please post your answers for the CODE GOLF (December 2015) competition in this thread.

    The questions for this competition can be found in the December 2015 newsletter.
    [click here to view newsletter]

    All answers will be soft deleted once posted and the thread will be locked on Sunday 17th January 2016 to prevent further answers.

    Good Luck!
    Last edited by S O; December 29th, 2015, 18:55.

  • #2
    Re: CODE GOLF - December 2015 Answers

    Answer to VBA question:
    Code:
    Public Function GetTaxYear(varDate As Date) As String
        If Month(varDate) < 4 Or (Month(varDate) = 4 And Day(varDate) < 5) Then GetTaxYear = Year(varDate) - 1 & "/" & Right(Year(varDate), 2) Else GetTaxYear = Year(varDate) & "/" & Right(Year(varDate) + 1, 2)
    End Function
    EDIT:

    Code:
    Option Explicit
    
    Public Function GetTaxYear(varDate As Date) As String
        If Month(varDate) < 4 Or (Month(varDate) = 4 And Day(varDate) < 5) Then
            GetTaxYear = Year(varDate) - 1 & "/" & Right(Year(varDate), 2)
        Else: GetTaxYear = Year(varDate) & "/" & Right(Year(varDate) + 1, 2)
            'not in posted code
        End If
    End Function
    Formula Solution:

    Assumes the "given columnd number" is in A1

    =IFERROR(MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1),2)-2),NA())

    That is 63 characters (or 62 if equals sign not counted)
    Last edited by S O; January 5th, 2016, 01:13. Reason: Added formula solution
    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Comment


    • #3
      Re: CODE GOLF - December 2015 Answers

      Hello again,
      Here's my proposal for the Question 1 (VBA):

      Code:
      Public Function GetTaxYear(varDate As Date) As String    
      GetTaxYear = Year(varDate) - (Year(varDate) < Year(varDate + 270)) - 1 & "/" & Right(Year(varDate) - (Year(varDate) < Year(varDate + 270)), 2)
      End Function

      Here's my proposal for question 2 (formula)
      Assuming column value entered in A1:
      Code:
      =IFERROR(SUBSTITUTE(LEFT(ADDRESS(1,A1),4),"$",""),NA())
      55 characters long
      Last edited by S O; January 4th, 2016, 10:27.

      Comment


      • #4
        Re: CODE GOLF - December 2015 Answers

        65 for me - A1 contains the column number:
        =IF(ISERR(ADDRESS(1,A1)),NA(),SUBSTITUTE(ADDRESS(1,A1,4),"1",""))
        Rory
        Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

        Comment


        • #5
          Re: CODE GOLF - December 2015 Answers

          Code VBA answer
          Code:
          Public Function GetTaxYear(varDate As Date) As String
          GetTaxYear = Year(varDate) + (Format(varDate, "mdd") < 406) & "/" & Format(varDate, "yy") - (Format(varDate, "mdd") > 405)
          End Function
          Formula solution:
          =IF(ISERR(ADDRESS(1,A1)),NA(),SUBSTITUTE(ADDRESS(1,A1,4),1,""))


          63 characters
          Last edited by S O; January 5th, 2016, 18:09. Reason: Merged - Comp answers
          Best Regards,
          Luke M
          =======
          "A little knowledge is a dangerous thing."

          Comment


          • #6
            Re: CODE GOLF - December 2015 Answers

            for compatability between all versions... Assuming row 1 is blank.... and A2 contains given column number

            =IF(A2>COUNTBLANK(1:1),NA(),SUBSTITUTE(ADDRESS(1,A2,4),1,""))
            Where there is a will there are many ways. Finding one that works for you is the challenge!

            MS Excel MVP 2010-2016

            Comment


            • #7


              Re: CODE GOLF - December 2015 Answers

              Code:
              Public Function GetTaxYear(varDate As Date) As String
                  GetTaxYear = Year(varDate) + (Format(varDate, "mdd") < 406) & "/" & Mid(Year(varDate) - (Format(varDate, "mdd") > 405), 3)
              End Function

              =SUBSTITUTE(IF(ISERR(ADDRESS(1,A2)),NA(),ADDRESS(1,A2,4)),1,)
              Cheers,

              S M C

              Click To Read: How To Use Tags In Your Threads/Posts
              Please take time to read Forum Rules before posting
              Message To Cross Posters

              Comment

              Working...
              X