CODE GOLF - December 2015 Answers

**S O** · Dec 18th 2015

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!

**KjBox** · Dec 23rd 2015

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)

GCExcel · Jan 4th 2016

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

**rory** · Jan 4th 2016

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

**Luke M** · Jan 4th 2016

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

**NBVC** · Jan 15th 2016

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]

**S M C** · Jan 16th 2016

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

Share