Bob,

The formula depends on what 'add one month' specifically means - what if your date is May 31 - do
you want June 30, or July 1

This is July1:
=DATE(YEAR(J2), MONTH(J2)+1,DAY(J2))

This returns June 30
=DATE(YEAR(J2),MONTH(J2)+1,IF(TRUE,DAY(DATE(YEAR(J 2),MONTH(J2)+2,0)),DAY(J2)))

For the single quote, you need to use

="'" & TEXT( Formula, "yyyy/mm/dd") & "'"

Replace "Formula" with the formula that you are using.

HTH,
Bernie
MS Excel MVP


"Bob" wrote in message
...
I have a column (J) which contains a date in the format "20/06/2009".

I need to do 3 things to it: - firstly add one month to the date //
secondly format the date as "2009/07/20" and finally put single quotes
around the date so it looks like " '2009/07/20' "

Whilst by using =J2+31 gives me the added month and by formatting the
cell using special yyyy/mm/dd gives me the correct look of the date
but as soon as i then try and put the sinlge quote around the date
using ="'"&(J2+31)&"'" then it returns an answer of " '40032' "

How can i best resolve this please. (I need the single quote as the
date is going to be copied into another program) I have a column of
aboout 400 rows for which i need to drag this formula down.

With thanks

Bob