Solved excell expiration date please

February 23, 2013 at 12:41:33
Specs: Windows Vista
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 More: excell expiration date please

Report •

#1
February 24, 2013 at 08:09:16
See how this works:

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

MIKE

http://www.skeptic.com/


Report •

#2
February 24, 2013 at 08:35:54
✔ Best Answer
A shorter version of the same thing:

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

MIKE

http://www.skeptic.com/


Report •

#3
February 24, 2013 at 08:48:28
Thanks mike, I m gonna try it.

Report •

Related Solutions

#4
February 24, 2013 at 09:15:59
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.


Report •

#5
February 24, 2013 at 09:29:51
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

http://www.skeptic.com/


Report •

#6
February 24, 2013 at 09:54:44
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.


Report •

#7
February 24, 2013 at 10:42:48
In my original formula I used the =DATEVALUE()
but I shortened it by removing it,

Just goes to show,
Sometimes bigger is better.

MIKE

http://www.skeptic.com/


Report •

#8
February 24, 2013 at 14:34:40
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.


Report •

Ask Question