Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
B2AB2A 

How to create Formula field to calculate amount of days in the next month

How would you create a Formula field to calculate amount of days in the next month.  So for example the calculation/value of this formula field should equal "31" since next month has 31 days (August).

 

Any help would be great.

 

Thanks.

 

Best Answer chosen by Admin (Salesforce Developers) 
JavajJavaj

Here is simple single line formula . 

 

To calculate the number of days in current month, use this following formula (copy and paste)

 

     DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)

 

 

For next month :

 

    DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1)

 

  let me know if you have any issues. 

Message Edited by Javaj on 07-22-2009 03:54 PM

All Answers

JakesterJakester

I think the best way to do this is sort of brute force. Untested code, but something like:

 

 

if (
and(mod(year(today()),4)=0,month(today())+1=2),29,
case(month(today())+1
,2,28
,4,30
,6,30
,9,30
,11,30,31
)
)
 
 
 
 


JavajJavaj
Here is simple single line formula . 
 
To calculate the number of days in current month, use this following formula (copy and paste)
 
     DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)
 
 
For next month :
 
    DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1)
 
  let me know if you have any issues. 
Message Edited by Javaj on  07-22-2009 03:54 PM
This was selected as the best answer
BlankaBlanka
Has anybody tried to calculate the business days passed since a specific date?
 For instance I want to know today (July 24,2009) how many business days have passed since July 1,2009. I would expect the formula to give me the following result:18. 
 
Any ideas how to create the formula?
 
Thanks 


BlankaBlanka
OK, got it....I just wanted to post it for anybody who might need it in the future....
 
 
  CASE(MOD( Start_Date__c - DATE(2009,7,1),7), 
0 , CASE( MOD( TODAY() - Start_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( TODAY() - Start_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( TODAY() - Start_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( TODAY() - Start_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( TODAY() - Start_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( TODAY() - Start_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( TODAY() - Start_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( TODAY() - Start_Date__c )/7)*5)
 
 
================================================================== 
Good luck!
 
Blanka 



 


B2AB2A
Thanks for the input, i'm sure some users / admins may need this !@


SeAlVaSeAlVa
Javaj, what about December? (Month + 1 = 13 :S )


David RightscorpDavid Rightscorp
yes what about december?? it appears that december gives an issue??


Alan McCarthyAlan McCarthy
Hi. I just had the same issue and eventually ended up where the last post ends. Does anyone know how to resolve the problem with December? Once it crosses the year mark it results in some errors. 


Alan McCarthyAlan McCarthy
I've almost got a solution with this formula but it still errors out due to the "+1" in the Month Function at the end, despite that the formula should stop on 31 from the Case function (or so I believe). 

CASE(MONTH(DATEVALUE(CreatedDate)),12,31,11,30,DAY(DATE(YEAR(DATEVALUE(CreatedDate)),MONTH(DATEVALUE(CreatedDate))+1,1)-1))

Any thoughts?


Steve HamiltonSteve Hamilton
December definitely breaks the solution's formula.  Here is the formula that I eventually arrived at.  It adds a specific test for the month of December (I could have made the test use "=" instead of ">" but it seems to work well enough).

DAY(DATE(YEAR(TODAY()),(IF(MONTH(TODAY())+1 > 12, 1, MONTH(TODAY())+1)),1)-1)