Excel and word macro about month end dates

October 30, 2011 at 00:57:32
Specs: Windows XP
=IF(15-(LEFT(TEXT(AN2,"DDMMYYYY"),2))>=0,CONCATENATE(15,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8)),CONCATENATE(30,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8))) ----- this was my excel formula, its a macro created with word link, my question was in this formula only I need change that the month end 30 because the macro created like this, because according to month end its 30 / 31 and 28 every time we have to change the month end, in 30 there is no need to change but in 31 and 28 we have to change mannually in word. So this I want to change Can u help me in this

See More: Excel and word macro about month end dates

Report •


#1
October 30, 2011 at 05:48:47
Don't forget Leap Year February has 29.

Report •

#2
October 30, 2011 at 17:50:23
I'm not sure if this helps since I don't know when or why you are using this formula, but this formula will return the number of days in the current month:

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

This will return the number of days in the Month of the date in A1:

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

Depending on your needs, perhaps you could substitute one of those two formulas for the 30 in your formula.

Untested:

=IF(15-(LEFT(TEXT(AN2,"DDMMYYYY"),2))>=0,
CONCATENATE(15,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8)),
CONCATENATE(DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1),
RIGHT(TEXT(AN2,"DD-MM-YYYY"),8)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question