DateFunction

  • Thread starter Thread starter SusanM
  • Start date Start date
S

SusanM

Hello Everyone,

Need help please with a formula.
EX: Cell A1 will be 5/23/04, Cell A2 will be 6/15/04 and
there may be up to 1,000 diff. dates in column A.
Need to add 6 months to the date in column B and add a
year in column C. Is it possible to have a formula to
copy down? I am able to do a single date, but a copy
would save much time.

Thank you everyone for your time. Much appreciated.
SusanM
 
Hi Susan!

To add 6 months use:

=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY($A1),DAY(DATE(YEAR(A1),MONTH(A1)+6+1,0)))
)

Be sure for copying down purposes that you use $A1 as shown.

Be aware that:

=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
Will return problem of interpretation where A1 day number is >=29 and where
that day number doesn't exist 6 months hence.

The preferred formula will return the last day of the month if that day
doesn't exist.

To add a year is easier:

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

A picky note, says watch out if A1 is 29th February because this will return
1-March. You might want to adjust for that but probably not necessary.


--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top