Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format plus calc
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format plus calc
="'"&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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format plus calc
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format plus calc
Bob,
Ooops..... the second formula should be =DATE(YEAR(J2),MONTH(J2)+1,IF(DAY(DATE(YEAR(J2),MO NTH(J2)+1,DAY(J2))) <DAY(J2),DAY(DATE(YEAR(J2),MONTH(J2)+2,0)),DAY(J2 ))) Sorry about that.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format plus calc
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ND TO CALC FROM ORIG DATE TO NEXT DATE RECEIVED? | Excel Discussion (Misc queries) | |||
#VALUE in Date calc | Excel Worksheet Functions | |||
Calc End Date | Excel Worksheet Functions | |||
Date Calc. HELP | New Users to Excel | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |