|
Forcing a specific date format
I've set the date format within the cells to appear as dd-mon-yy
(12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Likely because it should be dd-mmm-yy, not dd-mon-yy.
************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Hi Anne,
When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. -- Thanks, Diane "Anne Troy" wrote: Likely because it should be dd-mmm-yy, not dd-mon-yy. ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Forcing a specific date format
Update: I tried the custom and "dd-mmm-yy" - and it still does not change an
entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. -- Thanks, Diane -- Thanks, Diane "Anne Troy" wrote: Likely because it should be dd-mmm-yy, not dd-mon-yy. ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Forcing a specific date format
Hi Diane
You could enter in another column =MID(A1,FIND(" ",A1)+1,2)&"-"&LEFT(A1,FIND(" ",A1)-1)&"-"&RIGHT(A1,4) Copy down as required. Once complete, copy the whole range of data and Paste SpecialValues to convert from formulae to actual dates. Regards Roger Govier DianeMcP wrote: Update: I tried the custom and "dd-mmm-yy" - and it still does not change an entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. |
Forcing a specific date format
Diane: It sounds like maybe those entries are being seen as text? Can you
check for us? Copy any BLANK cell. Select those weird cells. Hit Edit--Paste special, Add. Then reformat however you want again. Does that work? ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... Update: I tried the custom and "dd-mmm-yy" - and it still does not change an entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. -- Thanks, Diane -- Thanks, Diane "Anne Troy" wrote: Likely because it should be dd-mmm-yy, not dd-mon-yy. ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Forcing a specific date format
Hi Roger,
In your sample, is the A1 supposed to be the date column? or should I substitute the A1 for the actual column being used for dates? -- Thanks, Diane "Roger Govier" wrote: Hi Diane You could enter in another column =MID(A1,FIND(" ",A1)+1,2)&"-"&LEFT(A1,FIND(" ",A1)-1)&"-"&RIGHT(A1,4) Copy down as required. Once complete, copy the whole range of data and Paste SpecialValues to convert from formulae to actual dates. Regards Roger Govier DianeMcP wrote: Update: I tried the custom and "dd-mmm-yy" - and it still does not change an entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. |
Forcing a specific date format
Hi Anne,
Nope, that didn't work. I've tried the setting for date and picking an existing sample of 14-Mar-01 and also custom and typing in dd-mmm-yy. It still keeps the incorrect date format. Any other ideas? -- Thanks, Diane "Anne Troy" wrote: Diane: It sounds like maybe those entries are being seen as text? Can you check for us? Copy any BLANK cell. Select those weird cells. Hit Edit--Paste special, Add. Then reformat however you want again. Does that work? ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... Update: I tried the custom and "dd-mmm-yy" - and it still does not change an entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. -- Thanks, Diane -- Thanks, Diane "Anne Troy" wrote: Likely because it should be dd-mmm-yy, not dd-mon-yy. ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Forcing a specific date format
Is it possible to see your files? Send to ng@ (my website).
************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... Hi Anne, Nope, that didn't work. I've tried the setting for date and picking an existing sample of 14-Mar-01 and also custom and typing in dd-mmm-yy. It still keeps the incorrect date format. Any other ideas? -- Thanks, Diane "Anne Troy" wrote: Diane: It sounds like maybe those entries are being seen as text? Can you check for us? Copy any BLANK cell. Select those weird cells. Hit Edit--Paste special, Add. Then reformat however you want again. Does that work? ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... Update: I tried the custom and "dd-mmm-yy" - and it still does not change an entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. -- Thanks, Diane -- Thanks, Diane "Anne Troy" wrote: Likely because it should be dd-mmm-yy, not dd-mon-yy. ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Forcing a specific date format
Just sent it, Anne
-- Thanks, Diane "Anne Troy" wrote: Is it possible to see your files? Send to ng@ (my website). ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... Hi Anne, Nope, that didn't work. I've tried the setting for date and picking an existing sample of 14-Mar-01 and also custom and typing in dd-mmm-yy. It still keeps the incorrect date format. Any other ideas? -- Thanks, Diane "Anne Troy" wrote: Diane: It sounds like maybe those entries are being seen as text? Can you check for us? Copy any BLANK cell. Select those weird cells. Hit Edit--Paste special, Add. Then reformat however you want again. Does that work? ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... Update: I tried the custom and "dd-mmm-yy" - and it still does not change an entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone? Diane Hi Anne, When I went back into the spreadsheet, I just chose one of the date formats that were already there, and it showed 04-Mar-05....it was probably me that assumed that the formatting in the code was dd-mon-yy. I've gone back into it and used "custom" and put in dd-mmm-yy. -- Thanks, Diane -- Thanks, Diane "Anne Troy" wrote: Likely because it should be dd-mmm-yy, not dd-mon-yy. ************ Anne Troy www.OfficeArticles.com "DianeMcP" wrote in message ... I've set the date format within the cells to appear as dd-mon-yy (12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12 2005 to 12-Oct-05? If it is supposed to do that, why are my cells not automatically reformatting to the syntax I want? -- Thanks, Diane |
Forcing a specific date format
Hi Diane
I assumed the dates were in column A and started at A1. Substitute your actual cell references into he formula e.g. if your dates are in column F starting at row 3, then change the formula to =MID(F3,FIND(" ",F3)+1,2)&"-"&LEFT(F3,FIND(" ",F3)-1)&"-"&RIGHT(F3,4) hope this is clear, but if not post back. Regards Roger Govier DianeMcP wrote: Hi Roger, In your sample, is the A1 supposed to be the date column? or should I substitute the A1 for the actual column being used for dates? |
| All times are GMT +1. The time now is 05:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com