The number of days until the end of the month

Question

For example, I have Start Date 29.05.2019, and Finish Date 4.06.2019. I have a formula field which displays 6 days between these 2 dates. But how can I count the number of days from Start Date until the end of the month? I need to get 3 days, including 29.05, 30.05 and 31.05. My reverse formula is here:

Days__c -((TODAY()-DAY(TODAY())+1) - Start_Date__c)

This is for

AND(MONTH(TODAY()) > MONTH(Start_Date__c), MONTH(TODAY()) = MONTH(Finish_Date__c))

How to do the same trick with

AND(MONTH(TODAY()) = MONTH(Start_Date__c), MONTH(TODAY()) < MONTH(Finish_Date__c))

?

Answer 1

First thing you have to do is find the End of the Current Month. You can get end of current month using this formula.

IF( MONTH( TODAY() ) = 12, 
DATE( YEAR( TODAY() ), 12, 31 ), 
DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1) - 1)

Once you got last day of the month just subtract the Start_Date__C to get the days till month end.

IF( MONTH( TODAY() ) = 12, 
DATE( YEAR( TODAY() ), 12, 31 ), 
DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1) - 1) - Start_Date__c

Answer 2

Date/DateTime manipulation in formulas is generally pretty cumbersome, but there are some things that help out here.

• There is a DATE() function
• You can add and subtract integers from a DATE value in a formula to change the date

So, if you were able to get the first day of the month after your start date, and subtract 1, you would get the last day of the month of your start date.

From there, it's simple date subtraction.

With this approach, you'll need to take some care to handle start dates in December (because 12 + 1 = 13 = not a valid month number)