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!

  • 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)

    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.

  • 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

  • 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

  • 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

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: CODE GOLF - December 2015 Answers


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


    [COLOR="#0000FF"]=IF(A2>COUNTBLANK(1:1),NA(),SUBSTITUTE(ADDRESS(1,A2,4),1,""))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • 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,)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!