End of month variation
Forums
In the IDEA googlegroups forum someone posted that they were looking for a formula in IDEA that performed the following Excel function:
=IF((DAY(A45)=1),EOMONTH(DATE( YEAR(A45)+60,MONTH(A45),DAY( A45)),-1),EOMONTH(DATE(YEAR( A45)+60,MONTH(A45),DAY(A45)), 0
Now the premise was that you are looking for the last day x number of years in the future (in this case 60 years) and that if you were born on the 1st day of the month the equation would return the last day of the previous month. Instead of writing an equation in IDEA I decided to create a custom function. The main reason I did this, is that even though I could write it using the equation editor the equation editor lacks much of the formatting of a program and it can be hard to read, even the excel equation above would probably take most knowledgeable readers a few minutes to understand. Creating a custom function allowed me to format so that it was nice and readable. The custom function takes two parameters, the date and the number of years in the future. It first tests to make sure that the number of years is greater than 0 if not it returns "00000000", so no date. It then extract into different variables the day, month and year. First it tests if the day falls on the 1st, if it does it next checks to see if the month is January, as we are looking at any dates following on the 1st returning the last day of the previous month, for the month of January we also have to roll back the year by one. For any other months in which the day is on the first we roll it back by one. We then add the number of years to the year variable and then extract the last of the month for the future date. Finally the script converts it to a character in the following format "YYYYMMDD" so that it can be easily changed into a date format by the @ctod function in IDEA. The following is the script so you can see how I went about doing this, does anyone have a better way to do this? I would be interested hearing.
Option Explicit
Function Eomonth_Special(testDate As Date,YearsToAdv As Double) As String
Dim intDay As Integer
Dim intMonth As Integer
Dim txtMonth As String
Dim intYear As Integer
Dim txtNewDate As String
If YearsToAdv < 1 Then
eomonth_special ="00000000"
Else
intDay = Day(testDate)
intMonth = Month(testDate)
intYear = Year(testDate)
'check to see if the month is Jan and if so set it to Dec else subtract it
If intDay = 1 Then
If intMonth = 1 Then
intMonth = 12
intYear = intYear - 1 + YearsToAdv
Else
intMonth = intMonth - 1
intYear = intYear + YearsToAdv
End If
Else
intYear = intYear + YearsToAdv
End If
intDay = ilastDayofmonth(intMonth,intYear+YearsToAdv)
If intMonth < 10 Then
txtMonth ="0" & intMonth
Else
txtMonth = intMonth
End If
txtNewDate = intYear & txtMonth & intDay
eomonth_special =txtNewDate
End If
End Function
Is there an option to find
Is there an option to find the "end of month" for the same year? As of now, I can only find the end of month of the next 'x' year(s). Please help!