# Thread: Days in Month (97)

1. ## 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?

2. ## 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. ## 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>

4. ## 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. ## 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.

#### Posting Permissions

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