Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
B2A

# 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.

Javaj

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

Jakester

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

Javaj

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
Blanka

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

Blanka

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

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

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

David Rightscorp
yes what about december?? it appears that december gives an issue??
Alan 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 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 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)