On 7 July, 14:27, Stefi wrote:
="'"&TEXT(DATE(YEAR(J2),MONTH(J2)+1,DAY(J2)),"y yyy/mm/dd")&"'"
Regards,
Stefi

„Bob” ezt írta:



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- Hide quoted text -

- Show quoted text -

Thanks Stefi, spot on just what I wanted.

Bob