# Calculation of Days in Month

#### Brenkenathan

##### Board Regular
Is there any way that excel automatically calculates the amount of days in a month?
Here is my problem

I have a spreadsheet to deteremine amount of money to pay for rent.

I have the following columns and I am trying to take out the days in the month colum

-I have a date signed colum
-a Plus 45 days colum because Rent doesnt start to 45 days later(=date signed+46)
-A Current Rent Amount Colum
-Days in month colum
-Per Diem Rate Colum (is Current Rent/days in month)
-Pro Rated Days (If signed date is 9/20/02, calculates days till end of month)
-Rent Due
I want this sheet to be made as simple as I can(for other people are going to be using it) and I dindt know if there is a way to make excel calculate those days for the Per Diem so we arent forced to...
Thanks

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
On 2002-09-20 07:31, Brenkenathan wrote:
Is there any way that excel automatically calculates the amount of days in a month?
...

For a month begin date in cell A1...

=DAY(EDATE(A1,1)-1)

For any date in cell A1 use...

=DAY(EDATE(TEXT(A1,"m-yyyy")+0,1)-1)

Note: EDATE is provided by the Analysis ToolPak. If not already installed see the Excel Help topic for "Install and use the Analysis ToolPak".
This message was edited by Mark W. on 2002-09-20 07:57

This message was edited by Scott R on 2002-09-20 10:15

EOMONTH is also in Analysis Toolpak.

=DAY(EOMONTH(A1,0))
This message was edited by Scott R on 2002-09-20 09:27

Thanks for all the help...I layed it out as following and it works great...
Row 1 is labels
A2=Signing Date
B2=Current Rent Amount
C2=Date Plus 45days (A2+46)
D2=B2/(DAY(EOMONTH(C2,0)))
E2=DAY(EOMONTH(C2,0)-C2+1)
F2=E2*D2

Ok so here is where I get a problem, when I do math everything is rounded....How do I solve that problem becuase it cause a costing me some times 6 cents and over...Which if this happens a few thousand times, will cause a problem

Check to see that Precision as Displayed is unchecked using menu commands Tools Options Calculation.

No luck with that one....If I create that spread sheet and put the date as 11/20/02 and the amount as 1600 I get a Per Diem of 51.61 for 28 days. And a Prorated Amount of 1445.16 when it should be 1445.08, any suggestions

E2=DAY(EOMONTH(C2,0)-C2+1)

...costing me some times 6 cents and over.
The daily Per Diem in your example is approx \$51.6129. If you round your Per Diem to 2 decimals then you will be off, either over OR under.

I would change E2 to =EOMONTH(C2,0)-C2+1

Apparently It isnt multiplying the currect value of a cell....I try simple multiplication and it doesnt work
It take e2*d2
E2=18
D2=51.61
and still it comes up with 929.03

I mean it picked up that D2 was 51.61 and was correct but when they multiply it goes nuts

On 2002-09-20 11:17, Brenkenathan wrote:
Apparently It isnt multiplying the currect value of a cell....I try simple multiplication and it doesnt work
It take e2*d2
E2=18
D2=51.61
and still it comes up with 929.03

I mean it picked up that D2 was 51.61 and was correct but when they multiply it goes nuts

D2 displays as 51.61 but internally is stored as 51.6129032258064 (see for yourself by selecting D2, hitting F2 & then F9. If you'd rather have 51.61 rounded, enter this in D2:
=ROUND(B2/DAY(EOMONTH(C2,0)),2)

Replies
1
Views
158
Replies
3
Views
354
Replies
11
Views
507
Replies
0
Views
522
Replies
2
Views
176

1,219,959
Messages
6,151,153
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back