Date Fomula Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
Click to expand...

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
 
Back
Top