Wednesday, July 22, 2009

How to Calculate the Number of Days in a Month

Step 1: Create a Formula Field (Number)

Step 2: Paste this formula : DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)

Function :

Where MONTH(TODAY()) is the month for which you want to return the number of days, and YEAR(TODAY()) is the year. This function calculates the first day of the next month and subtracts 1, which equals the last day of the current month. The DAY function then gives you the day of the month.





Posted by at

8 comments:

  1. ColumbusCreche.orgNovember 26, 2012 at 8:21 AM

    Thank you for the formula. The problem is that in December, MONTH(TODAY())+1 equals 13, which is invalid, and causes an error. How would you handle that problem?

    ReplyDelete
  2. AnonymousDecember 5, 2012 at 11:18 AM

    Receiving that error now, any solution? What about an IF statement and "12"? Thoughts

    ReplyDelete
  3. sfdcGeekOctober 27, 2013 at 1:25 AM

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

    ReplyDelete
  4. DAvidDecember 2, 2014 at 11:03 AM

    Yes it's December now and i'm having this issue! The IF formula doesn't seem to work.....help!

    ReplyDelete
    Replies
    1. UnknownAugust 10, 2015 at 2:26 AM

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

      Delete
    2. UnknownAugust 10, 2015 at 2:37 AM

      sfdcGeek's formula returns the last day of the specified month as a date, my formula returns the last day of the current month as a number.

      Delete
    3. UnknownDecember 1, 2015 at 9:53 PM

      Very good man..kudos !!!

      Delete
  5. UnknownAugust 10, 2015 at 1:50 AM

    This comment has been removed by the author.

    ReplyDelete

Subscribe to: Post Comments (Atom)

Followers