Skip to main content

End of month variation

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

mail.jamy Thu, 11/22/2018 - 08:15

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!

Brian Element Mon, 11/26/2018 - 11:02

In reply to by mail.jamy

This is a function in the Equation Editor called @LastDayOfMonth(Month, Year) that will return with the last day of the month and year you indicate.  Is this what you are looking for?

Brian

mail.jamy Wed, 11/28/2018 - 02:13

In reply to by Brian Element

Thanks Brian!
I finally ended up in a formula:
@ctod(@str(@month(date),2,0) + "/" +@str(@lastdayofmonth(@Month(date), @year(DATE)),2,0) + "/" +@str(@year(date),4,0), "MM/DD/YYYY")
 

Johschan1 Thu, 07/08/2021 - 17:15

In reply to by mail.jamy

Hi Brian there is a way that i can get the last working day of a month, for example my working days are from monday to friday i wan to get the last friday that i worked.
 
Regards