Date format plus calc

[Bob]

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

 

[Stefi]

="'"&TEXT(DATE(YEAR(J2),MONTH(J2)+1,DAY(J2)),"yyyy/mm/dd")&"'"
Regards,
Stefi

„Bob†ezt írta:

 

[Bernie Deitrick]

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(J2),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

 

[Bernie Deitrick]

Bob,

Ooops..... the second formula should be

=DATE(YEAR(J2),MONTH(J2)+1,IF(DAY(DATE(YEAR(J2),MONTH(J2)+1,DAY(J2)))
<>DAY(J2),DAY(DATE(YEAR(J2),MONTH(J2)+2,0)),DAY(J2)))

Sorry about that....

HTH,
Bernie
MS Excel MVP

 

[Bob]

="'"&TEXT(DATE(YEAR(J2),MONTH(J2)+1,DAY(J2)),"yyyy/mm/dd")&"'"
Regards,
Stefi

„Bob” ezt írta:









- Show quoted text -

Thanks Stefi, spot on just what I wanted.

Bob