CODE GOLF - December 2015 Answers

  • Re: CODE GOLF - December 2015 Answers


    Answer to VBA question:

    Code
    1. Public Function GetTaxYear(varDate As Date) As String
    2. 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)
    3. End Function


    EDIT:


    Code
    1. Option Explicit
    2. Public Function GetTaxYear(varDate As Date) As String
    3. If Month(varDate) < 4 Or (Month(varDate) = 4 And Day(varDate) < 5) Then
    4. GetTaxYear = Year(varDate) - 1 & "/" & Right(Year(varDate), 2)
    5. Else: GetTaxYear = Year(varDate) & "/" & Right(Year(varDate) + 1, 2)
    6. 'not in posted code
    7. End If
    8. 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
    1. Public Function GetTaxYear(varDate As Date) As String
    2. GetTaxYear = Year(varDate) - (Year(varDate) < Year(varDate + 270)) - 1 & "/" & Right(Year(varDate) - (Year(varDate) < Year(varDate + 270)), 2)
    3. End Function



    Here's my proposal for question 2 (formula)
    Assuming column value entered in A1:

    Code
    1. =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
    1. Public Function GetTaxYear(varDate As Date) As String
    2. GetTaxYear = Year(varDate) + (Format(varDate, "mdd") < 406) & "/" & Format(varDate, "yy") - (Format(varDate, "mdd") > 405)
    3. 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!


    MS Excel MVP 2010-2016

  • Re: CODE GOLF - December 2015 Answers


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



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