Excel formula required to calculate the remaining number of days of month

[sahar_sultana]

Hi,
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.


A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)

Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)

Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.

Please advise about formula for cell-D4. Thanks. <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig --><!-- edit note -->

 

[barry houdini]

Hello sahar sultana, welcome to MrExcel, try this formula in D4

=C1/(32-DAY(TODAY()+31))

 

[Fazza]

Maybe

=C1/(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-TODAY())

Or if for 1 April the answer should be 29, not 30

=C1/(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-TODAY())

 

[Fazza]

good one, Barry

 

[jbeaucaire]

Click on TOOLS > ADDINS and make sure the Analysis Toolpak has a checkmark next to it.

Then use this formula:

=C1/DAY(EOMONTH(TODAY(),0))-DAY(TODAY())

 

[barry houdini]

In fact my suggestion would give the wrong result for some dates near the end of the month so it would have to be more like

=C1/(33-DAY(TODAY())-DAY(TODAY()-DAY(TODAY())+32))

If you use EOMONTH as per jbeaucaire's suggestion then you don't need DAY, i.e. you can use

=C1/(EOMONTH(TODAY(),0)-TODAY()+1)