End of Month Date
In this post, it will show you how to create an expression to calculate the date the for end of the month from a specified date field. Below is an expression where [Date] is the field in which the expression is using to find the last day from.
addDays(string(year(addMonths([Date],1))) + '-' + if(month(addMonths([Date], 1)) < 10, '0', '') + month(addMonths([Date],1)) + '-01T00:00:00Z',-1)
This expression works by converting the date field into a string date format yyyy-mm-ddThh:mm:ssZ. As there are different days at the end of month day the first step would be to find the first day of the next month. To make it easier to understand, it would be useful to break it down into a more readable format.
year(next month) + '-' + IF month(next month) is less then 10 (October) + '0' ELSE nothing + month(next month) + '-01T00:00:00Z'
Example: [Date] = 11/03/2022 the expression will return 01/04/2022 (2022-04-01T00:00:00Z). The year for the next month will remain the same creating the string '2022-'. As the date format only accept two digit months a IF statement is needed to add the '0' for every single digit month which can be accomplished by adding '0' to every next month which is less then 10 or October leaving the string as '2022-0'. Then the number for the next month is added giving '2022-04'. Then comes the string which returns the first day of a month leaving the string as '2022-04-01T00:00:00Z' which is the first day of the next month.
The last step now is to subtract that date by one to get the last day of the month which can be done using the addDays() formula.
addDays(first date of next month, -1)
Please sign in to leave a comment.
Comments
0 comments