Calculate the last day of the next month

  • 11 February 2020
  • 3 replies
  • 286 views

Badge +2

Trying to calculate the last day of the next month.  I have tried using the excel formula =EOMONTH(Date,1). A cast formula and a SQL formula.  (I can't figure out the connection string and may not have access on the Excute SQL)  easiest would be a calculated column, but could do it in a workflow or a calculated variable on the form.  Is EOM a function in nintex and I am just missing it, or has anyone done this.

What I am running into is a situation where the due date is EOM but when I use Calculate date and add 1 month march is coming up 3/28/2020 instead of 3/31/2020.

 

Thank you for any help you can provide.


3 replies

Badge +3

Hello,


 


Not sure of what your case use is but if you are trying to determine the last day of the month for an item you can determine the the next month of the targeted date and subtract one day to have the desired result. I used the following below within a calculated column. This will obviously transfer easily within Forms. Calculate Date can bring in that calculated date from the particular list item in question. 


 


=(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY([Today's Date])))-(1) [Today'sDate] was a date and time column with todays date set as default but you can set your intended column there instead.


 


 

Badge +2

I think I may have found a soultion using a substring where I replace the day with 01 and the in the next step in the work flow add two months and subtract 1 day.


 


Example current date is 02/15/2020 I make 02/01/2020 and then in the next step add 2 months and subtract 1 day.  Result is 03/31/2020.  It would be nice to figure out a way to do it in 1 step, but currently I set a variable equal to current date, format to "MM/dd/yyyy" then use the substring and replace the day with 01 and then have to add 2 monthe and subtract a day.


 


Maybe someone has a better way.  


 

Userlevel 1
Badge +6

This is the formula I use to calculate the last day of the month in a rule to set a date field:


 


dateAdd("days",
dateDiff("TotalDays",dateNow(),date(
convertToNumber(formatDate(dateNow(),"YYYY")),
sum(convertToNumber(formatDate(dateNow(),"M"))+1)
,1)),
dateNow())

Reply