help about date & month function in Excel

[Guest]

I want to know about a function such as
=Sum( value / January * value)

I mean I need a value divided by total days of the month such as january,
february etc. & then multiplied again by a value. I want a function in which
I can put total days of the month say november automatically. I will be
greatful to hear from anybody.

 

[Guest]

=A1^2/DAY(EOMONTH(1/1/2007,0))

 

[Ron Rosenfeld]

I want to know about a function such as
=Sum( value / January * value)

I mean I need a value divided by total days of the month such as january,
february etc. & then multiplied again by a value. I want a function in which
I can put total days of the month say november automatically. I will be
greatful to hear from anybody.

If your month number is in A1, then:

=DAY(DATE(2007,A1+1,0))

will give the number of days in that month.

You should probably include the year also, as February changes.

With any date (e.g. 1/15/2007) in the desired month in A1, then the formula
would be:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
--ron

 

[Roger Govier]

Hi

If you have the Analysis Toolpak loaded, (Tools>Addins>Analysis Toolpak>
check box) then you have January or any other month you want in cell A1,
then

=DAY(EOMONTH((A1&0),0))
or without the Analysis Toolpak, then
=DAY(DATE(YEAR(TODAY()),MONTH(K3&0)+1,0))

These formulae would replace the month in your formula, or perhaps
insert either of them in say cell B1 then use
=(Value/B1)*Value

 

[Roger Govier]

Sorry, that second formula should have referred to A1 as well
=DAY(DATE(YEAR(TODAY()),MONTH(A1&0)+1,0))

 

[JE McGimpsey]

That will only works by chance for January, since using 1/1/2007 in the
EOMONTH portion is equivalent to

EOMONTH(0.00049825610363727,0)



Better:

EOMONTH(DATE(2007,1,1),0)

 

[Guest]

If you have a cell in time format than the end of the month is
=eomonth(mytime,0)

also
=eomonth(today(),0)

Here is a trick so you can enter the month number from 1 to 12 and get it to
work for every year including leap years.

==DAY(EOMONTH(DATE(YEAR(NOW())-1,12,1),A1)) where A1 is a month number 1 -
12. The second parameter of is an offset of the month with 0 being the
present month. You have to use a December date to get January to be an
offset of 1. To make usre it handle the leap year properly You have to put
in the present year.