Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Count Days in a Month

  1. #1
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count Days in a Month

    Dear all

    Could someone explain below formula to me please.
    Code:
    =DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
    The red parts, no idea.

    Thank you very much

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Count Days in a Month

    Quote Originally Posted by Vanda_a View Post
    Dear all

    Could someone explain below formula to me please.
    Code:
    =DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
    The red parts, no idea.

    Thank you very much
    1. Given a date, the formula constructs first a first day date following the month/year of A1.

    A1 = 4/20/2015

    ==> 5/1/2015

    Note that this is DATE applied to 2015, 5 (MONTH of A1, which is 4, plus 1) and 1.

    2. Subtracting 1 from the first day date of (1), that is,

    5/1/2015 minus 1, we get:

    ==> 4/30/2015

    3) DAY applied to (2), we get:

    ==> 30

    which is days April (of 2015) counts.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Days in a Month

    Quote Originally Posted by Aladin Akyurek View Post
    1. Given a date, the formula constructs first a first day date following the month/year of A1.

    A1 = 4/20/2015

    ==> 5/1/2015

    Note that this is DATE applied to 2015, 5 (MONTH of A1, which is 4, plus 1) and 1.

    2. Subtracting 1 from the first day date of (1), that is,

    5/1/2015 minus 1, we get:

    ==> 4/30/2015

    3) DAY applied to (2), we get:

    ==> 30

    which is days April (of 2015) counts.
    Understand it now. Thank you very much.

  4. #4
    Board Regular
    Join Date
    Oct 2013
    Location
    Belo Horizonte, Brazil
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count Days in a Month

    Vanda_a, Good morning.

    It's a very good explanation about this formula.
    Aladin Akyurek, congratulations!

    I suggest you to use this formula:

    =DAY(EOMONTH(A1),0)

    I think it's more simple.

    I hope it helps.
    Have a nice day.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Count Days in a Month

    Quote Originally Posted by Vanda_a View Post
    Understand it now. Thank you very much.
    You are welcome.

    Since EOMONTH is directly available in the recent Excel versions...

    =DAY(EOMONTH(A1,0))

    can also be invoked to the same effect.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular
    Join Date
    Nov 2009
    Posts
    567
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Days in a Month

    Alternative to last day of previous month from today:

    =DAY(TODAY()-DAY(TODAY()))

    or a date in A1

    =DAY(A1-DAY(A1))

  7. #7
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Days in a Month

    Quote Originally Posted by Aladin Akyurek View Post
    You are welcome.

    Since EOMONTH is directly available in the recent Excel versions...

    =DAY(EOMONTH(A1,0))

    can also be invoked to the same effect.
    Yeah... This one is alot easier.

    Thanks again

  8. #8
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Days in a Month

    Quote Originally Posted by Marcílio_Lobão View Post
    Vanda_a, Good morning.

    It's a very good explanation about this formula.
    Aladin Akyurek, congratulations!

    I suggest you to use this formula:

    =DAY(EOMONTH(A1),0)

    I think it's more simple.

    I hope it helps.
    Have a nice day.
    Thanks

  9. #9
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Days in a Month

    Quote Originally Posted by snb_ View Post
    Alternative to last day of previous month from today:

    =DAY(TODAY()-DAY(TODAY()))

    or a date in A1

    =DAY(A1-DAY(A1))
    Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •