I know this might be easy but i was wondering if someone could help write an if then statement on excell. I need the end of the month date to end on the 30th of every month except february which would be 28th. example if A1 was 1/13/13 and i have a 1 yr expiration, then A2 would be 1/30/14.

See how this works: =IF(MONTH(A1)<>2,MONTH(A1)&"/"&30&"/"&YEAR(A1)+1,MONTH(A1)&"/"&28&"/"&YEAR(A1)+1)

MIKE

A shorter version of the same thing: =IF(MONTH(A1)<>2,MONTH(A1)&"/"&30&"/"&YEAR(A1)+1,2&"/"&28&"/"&YEAR(A1)+1)

MIKE

Thanks mike, I m gonna try it.

Shorter still, and returns a date, not text: =IF(DAY(EOMONTH(A1,12))<=30,EOMONTH(A1,12),EOMONTH(A1,12)-1)

You must have the Analysis ToolPak installed in order to use the EOMONTH function.

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

returns a date, not text:OK, try this which will return a date also:

=DATEVALUE(IF(MONTH(A1)<>2,MONTH(A1)&"/"&30&"/"&YEAR(A1)+1,2&"/"&28&"/"&YEAR(A1)+1))

MIKE

Based on your 2 previous responses, I thought we were trying to shorten the formula, not make it longer. ;-) BTW...my earlier suggestion will return 2/29 during Leap Years, so I changed it to force 28, since that's what the OP seems to be asking for.

=IF(MOD(DAY(EOMONTH(A1,12)),2)=1,EOMONTH(A1,12)-1,EOMONTH(A1,12))

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

In my original formula I used the =DATEVALUE()

but Ishortenedit by removing it,Just goes to show,

Sometimes bigger is better.MIKE

Well, the long and short of it (pun intended) is that the OP got to see more functions than I'll bet he thought he would! DATEVALUE

IF

DAY

MONTH

YEAR

MOD

EOMONTH

The CONCATENATION operator, &Plus formulas that return text that looks like a date and formulas that return actual dates.

What more could he ask for? ;-)

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

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History