Calculation of Days in Month

Brenkenathan

Board Regular
Joined
Sep 19, 2002
Messages
146
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows

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

Scott R

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows
EOMONTH is also in Analysis Toolpak.

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

Brenkenathan

Board Regular
Joined
Sep 19, 2002
Messages
146

ADVERTISEMENT

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
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows
Check to see that Precision as Displayed is unchecked using menu commands Tools Options Calculation.
 

Brenkenathan

Board Regular
Joined
Sep 19, 2002
Messages
146

ADVERTISEMENT

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
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows
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
 

Brenkenathan

Board Regular
Joined
Sep 19, 2002
Messages
146
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
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,144,330
Messages
5,723,740
Members
422,512
Latest member
MHau5

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top