Announcement

Collapse
No announcement yet.

Formula to calculate the number of days till end of month

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Formula to calculate the number of days till end of month

    Hi

    Im looking for a formula that will calculate the number of days left in a month when the formula looks at any day in the month.

    For example if A1 = 2006/05/14 then the formula in A2 that look at A1 must give the answer 17

    I tried every thing I know of but nothing wants to work.

    Thanks for you help

    Neels

  • #2
    Re: Formula to calculate the number of days till end of month

    I can think of 2 ways straight away. The first one requires that you have the Analysis Toolpak added in, and is:
    Code:
    =EOMONTH(A1,0)-A1
    and the other one is:
    Code:
    =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-A1
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

    Comment


    • #3
      Re: Formula to calculate the number of days till end of month

      Another way without ATP

      =DATE(YEAR(A1),MONTH(A1)+1,0)-A1

      you need to format the cell as General though
      HTH

      Bob

      Comment


      • #4
        Re: Formula to calculate the number of days till end of month

        Yes, Bob, my second one works that way too. I said that the first one needs the Analysis Toolpak, not my second one.
        Cheers, Glenn.

        Beauty is in the eye of the beer-holder.

        Comment


        • #5
          Re: Formula to calculate the number of days till end of month

          Originally posted by GlennUK
          Yes, Bob, my second one works that way too. I said that the first one needs the Analysis Toolpak, not my second one.
          Sorry I mis-read the VBA tag as suggesting in VBA (doh!). But at least I do show how to use aday number of 0, avoiding the extra subtraction <G>
          HTH

          Bob

          Comment


          • #6
            Re: Formula to calculate the number of days till end of month

            I think using the DATEDIF and NOW function will be best in this situation.


            =DATEDIF(A1,NOW(),"d")

            Comment


            • #7
              Re: Formula to calculate the number of days till end of month

              Fin Fang Foom,

              please explain how that would give the number of days until the end of the month?!?!?
              Cheers, Glenn.

              Beauty is in the eye of the beer-holder.

              Comment


              • #8
                Re: Formula to calculate the number of days till end of month

                Hi GlennUK,


                Aw I should've read the post more carefully. Your formulas would be efficient.

                Comment


                • #9
                  Re: Formula to calculate the number of days till end of month

                  Thanks a million GlennUK - It works like a dream - "I can imaging a world without writers like you" - This forum is unbelievable.

                  Comment

                  Working...
                  X