Date Fomula Question
 

I need to figure out a formula that will add 30 days to a month/day/ date but
return the year as 2007 if the date is before 2007 and just add 30 days if
it's a date after the today format.

clear as mud, 'eh .... maybe this would be easier ... (see below)

ColumnA B
is: would need to return

3/15/2002 4/15/2007
3/18/1998 4/18/2007
3/20/2006 4/20/2007
3/21/2007 4/21/2007
6/28/2008 7/28/2008
4/1/2003 5/1/2007
4/1/2010 5/1/2010

and so on, the dates in column A vary, and I have no problem adding the
extra 30 days, but it's the year that's throwing me .... any suggestions?

Donna

Date Fomula Question
 

=date(if(year(A1+30)=2008,2006,2007),Month(A1),Day (A1)+30)

"rainbowraven" wrote:

I need to figure out a formula that will add 30 days to a month/day/ date but
return the year as 2007 if the date is before 2007 and just add 30 days if
it's a date after the today format.

clear as mud, 'eh .... maybe this would be easier ... (see below)

ColumnA B
is: would need to return

3/15/2002 4/15/2007
3/18/1998 4/18/2007
3/20/2006 4/20/2007
3/21/2007 4/21/2007
6/28/2008 7/28/2008
4/1/2003 5/1/2007
4/1/2010 5/1/2010

and so on, the dates in column A vary, and I have no problem adding the
extra 30 days, but it's the year that's throwing me .... any suggestions?

Donna

Date Fomula Question
 

I'm not sure I understand correctly, but see if this works for you:

=IF(TODAY()A1,DATE(2007,MONTH(A1)+1,DAY(A1)),DATE (YEAR(A1),MONTH(A1),DAY(A1)+30))

HTH,
Elkar


"rainbowraven" wrote:

I need to figure out a formula that will add 30 days to a month/day/ date but
return the year as 2007 if the date is before 2007 and just add 30 days if
it's a date after the today format.

clear as mud, 'eh .... maybe this would be easier ... (see below)

ColumnA B
is: would need to return

3/15/2002 4/15/2007
3/18/1998 4/18/2007
3/20/2006 4/20/2007
3/21/2007 4/21/2007
6/28/2008 7/28/2008
4/1/2003 5/1/2007
4/1/2010 5/1/2010

and so on, the dates in column A vary, and I have no problem adding the
extra 30 days, but it's the year that's throwing me .... any suggestions?

Donna

Date Fomula Question
 

I should learn to read the whole message

=date(max(2007,year(A1),month(A1),day(A1)+30)

"bj" wrote:

=date(if(year(A1+30)=2008,2006,2007),Month(A1),Day (A1)+30)

"rainbowraven" wrote:

I need to figure out a formula that will add 30 days to a month/day/ date but
return the year as 2007 if the date is before 2007 and just add 30 days if
it's a date after the today format.

clear as mud, 'eh .... maybe this would be easier ... (see below)

ColumnA B
is: would need to return

3/15/2002 4/15/2007
3/18/1998 4/18/2007
3/20/2006 4/20/2007
3/21/2007 4/21/2007
6/28/2008 7/28/2008
4/1/2003 5/1/2007
4/1/2010 5/1/2010

and so on, the dates in column A vary, and I have no problem adding the
extra 30 days, but it's the year that's throwing me .... any suggestions?

Donna

Date Fomula Question
 

On Mon, 23 Apr 2007 12:50:00 -0700, rainbowraven
wrote:

I need to figure out a formula that will add 30 days to a month/day/ date but
return the year as 2007 if the date is before 2007 and just add 30 days if
it's a date after the today format.

clear as mud, 'eh .... maybe this would be easier ... (see below)

ColumnA B
is: would need to return

3/15/2002 4/15/2007
3/18/1998 4/18/2007
3/20/2006 4/20/2007
3/21/2007 4/21/2007
6/28/2008 7/28/2008
4/1/2003 5/1/2007
4/1/2010 5/1/2010

and so on, the dates in column A vary, and I have no problem adding the
extra 30 days, but it's the year that's throwing me .... any suggestions?

Donna

To add thirty days to some date in A6, and adjust so the result is not earlier
than 2007, use the formula:

=DATE(MAX(YEAR(A6),2007),MONTH(A6),DAY(A6)+30)

However, in your examples, you are NOT always adding 30 days. Sometimes you
are adding 31 days.

Perhaps what you want is:

=DATE(MAX(YEAR(A6),2007),MONTH(A6)+1,DAY(A6))-
(DAY(DATE(MAX(YEAR(A6),2007),MONTH(A6)+1,DAY(A6))) *
(DAY(DATE(MAX(YEAR(A6),2007),MONTH(A6)+1,DAY(A6))) <DAY(A6)))

All the stuff at the end is to adjust so that one month after Jan 31 will be
Feb 28 and not March 3.

Or if you have the Analysis Tool Pak installed, you could use:

=EDATE(DATE(MAX(YEAR(A6),2007),MONTH(A6),DAY(A6)), 1)






--ron