1

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))

?

3

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
  • 2
    N.B. This will return the difference between the last day of TODAY's month and the start date. Some modification is required if you want to get the difference between the last day of the same month as Start_Date__c and Start_Date__c. A start date of 2019-4-19 would return 42 for the last day of TODAY's month, and 11 for the last day of Start_Date__c's month. – Derek F May 2 at 15:31
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.