HELP - need to returns the current number of past month this year and ...

E

elz64

* it's APRIL - month is 4 but we had 4-1 monthes since Year begun. So.. 3

* want to have the the fraction of the current month : last day 30, first 1,
number of day in this month 30-1 +1, gives 30.

* imagine we are the 6 of Apr. this gives 6/30=0.2
so month since Jan 1 is 3.2

HOW to automatize this day by day. I mean if I came back on the worksheet
in 2 weeks or 3 monthes I want the logics to know this at any time for any
date. This suppose to be able to determine the current month, then process
it's number of days, the the fraction, only knowing TODAY ( )


thank you
 
N

Niek Otten

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
E

elz64

Thanks a lot ( I had to adapt it to French - " ; " instead of "," and to
translate key words)
and it's OK
 
R

Ron Rosenfeld

* it's APRIL - month is 4 but we had 4-1 monthes since Year begun. So.. 3

* want to have the the fraction of the current month : last day 30, first 1,
number of day in this month 30-1 +1, gives 30.

* imagine we are the 6 of Apr. this gives 6/30=0.2
so month since Jan 1 is 3.2

HOW to automatize this day by day. I mean if I came back on the worksheet
in 2 weeks or 3 monthes I want the logics to know this at any time for any
date. This suppose to be able to determine the current month, then process
it's number of days, the the fraction, only knowing TODAY ( )


thank you
Click to expand...

=MONTH(A1)-1+DAY(A1)/(32-DAY(A1-DAY(A1)+32))

You can substitute TODAY() for A1 in the above formula:

=MONTH(TODAY())-1+DAY(TODAY())/(32-DAY(TODAY()-DAY(TODAY())+32))


--ron
 
D

Duke Carey

If you need the month as an integer and the month fraction:

=MONTH(EOMONTH(TODAY(),-1))+(DAY(TODAY())/DAY(EOMONTH(TODAY(),0)))

This also requires the Analysis Toolpack
 
Top