Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Days in Month (97)

    I am making a prorating formula for rent payment subsidies. I need to be able to put in a date of the month, which month, and the year and then find out how many days are left in the month.

    A1=12
    B1=February
    C1=2001

    d1=A1/(DaysInMonth(B1 of C1))*rent

    Is there a function that can figure the DaysInMonth accurately if given the month and the year?

    Thanks in advance.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Days in Month (97)

    If you have a date in A1, the following formula will give you the remaining days left in the month of that date, after tyhe actual date.<pre> =(DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))-DAY(A1)</pre>

    If you have today's date in A1, that formula will return 8, the number of days remaining after today. If you have july 1st 2001 in A1, it will return 30. If you want to include the date itself, you will need to add 1 to the result.

    hope that assists

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Days in Month (97)

    Andrew has given you a formula that will work if you put the date into cell A1, which is really the best way to do this. You can use a format to make it display the way you want. However, if you must split the date into three cells like you showed, then this forumla will give you what you want:

    <pre>=(DAY(DATE(YEAR(DATEVALUE(B1&" "&A1&", "&C1)),MONTH(DATEVALUE(B1&" "&A1&", "&C1))+1,0)))-DAY(DATEVALUE(B1&" "&A1&", "&C1))
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Days in Month (97)

    To put the 3 cells into a date, you need to have the name February as a number 2. If cell A1=12, B1=February, C1=2001, D1=2, then =date(C1,D1,A1) gives the serial number for the date.
    I made a Vlookup table which has:
    in the 1st column the months January, February, ..., December
    in the 2nd column their numerical value - 1, 2, ... , 12
    in the 3rd column the number of days in the month 31, 28, ...
    I had this in the range C7:E18
    So
    in cell D1 I put =VLOOKUP(B1,$C$6:$D$17,2,FALSE)
    in cell E1 I put =DATE(C1,D1,A1)
    in cell F1 I put =VLOOKUP(B1,$C$6:$E$17,3,FALSE)
    in cell G1 I put =F1-A1

    Putting it altogether in one cell gives
    in H1 =VLOOKUP(B1,$C$6:$E$17,3,FALSE)-A1
    This doesn't take into account leap years of course

    Alternative:
    =DATE(C1,VLOOKUP(B1,$C$6:$D$17,2,FALSE)+1,1)-DATE(C1,VLOOKUP(B1,$C$6:$D$17,2,FALSE),A1)-1

    or
    =DATE(C1,D1+1,1) - date(C1,D1,A1) -1 if you have the month as a number

    Hope this isn't too convoluted
    Ruth

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Days in Month (97)

    Ruth: See my reply. You don't need the month as numeric to convert to a date value. The following works:

    <pre>=DATEVALUE(B1&" "&A1&", "&C1)
    </pre>


    Given that, the formula in my response will calculate the number of days left in the month with the month name in the cell.
    Legare Coleman

Posting Permissions

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