Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Formula to calculate the number of days till end of month

  1. #1
    Join Date
    30th January 2005
    Location
    South Africa
    Posts
    21

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd March 2006
    Location
    London
    Posts
    988

    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:
    VB:
    =EOMONTH(A1,0)-A1 
    
    
    and the other one is:
    VB:
    =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-A1 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    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

  4. #4
    Join Date
    2nd March 2006
    Location
    London
    Posts
    988

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

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

    Quote 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

  6. #6
    Join Date
    24th March 2005
    Posts
    507

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    2nd March 2006
    Location
    London
    Posts
    988

    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?!?!?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th March 2005
    Posts
    507

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    30th January 2005
    Location
    South Africa
    Posts
    21

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Formula For Number Of Days In A Month
    By hgus393 in forum EXCEL HELP
    Replies: 9
    Last Post: June 18th, 2008, 11:06
  2. Number of Specified Days in a Month
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: September 6th, 2005, 16:48
  3. Number of days in given month
    By Michael Avidan in forum EXCEL HELP
    Replies: 6
    Last Post: July 11th, 2004, 15:29
  4. Replies: 2
    Last Post: October 29th, 2003, 04:45
  5. Formula for Number of Days in Month
    By imported_Anonymous in forum EXCEL HELP
    Replies: 3
    Last Post: February 24th, 2003, 23:43

Bookmarks

Posting Permissions

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