0

End of Month custom column (EOMONTH formula)

Is there way to convert dates to End of Month date using formula like EOMONTH. In our system, we do not load data for Sundays or Holidays and I would like to use the EOMONTH formula to convert any non-month end dates to month end date. 

 

4 replies

null
    • NPANS
    • 10 days ago
    • Reported - view

    Long winded, but doable without a built-in function:

    • get the year: y=year(<your date>)
    • get the month: m=month(<your date>)
    • create a date as the first of that month: c=CreateDate( y , m , 1)
    • add a month to that date: a=AddMonths( c , 1 )
    • deduct a day from that month: AddDays( a , -1 )

    in one calc:

    AddDays( AddMonths( CreateDate(year(<your date>), month(<your date>),1),1),-1)
    • Jashanpreet_Sandhu
    • 2 days ago
    • Reported - view

    createdate does not work

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 days ago
      • Reported - view

       

      Yes, the CreateDate function isn't granular.

      Here's What you can do:

      1. Add 1 month, and then reduce the number of days in that added month date:

      Or if you prefer script:

      AddDays(
    AddMonths([Data].[Date],1),
            -1*DayNumber(
                    AddMonths([Data].[Date],1)
            )
        )

      There are other alternatives - I think this is the most elegant.

      • Jashanpreet_Sandhu
      • 2 days ago
      • Reported - view

       Yes I ended up using the similar logic. One change I had to do was to convert DayNumber to Integer & then multiply by -1. This seems to work well.

      Thanks

Login to reply

Content aside

  • Status Answered
  • 2 days agoLast active
  • 4Replies
  • 22Views
  • 3 Following