If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formatting Dates
I have looked through other threads but can't find an answer to my query. I
have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda |
#2
|
|||
|
|||
Formatting Dates
Hi Lynda,
What do you get, try doing text to columns for that imported dates or in an auxiliar column enter =Trim(A1) to trim any blank space then copy and paste values and format it see what happens Hope this helps "Lynda" wrote: I have looked through other threads but can't find an answer to my query. I have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda |
#3
|
|||
|
|||
Formatting Dates
You may be having problems with different international standards. It look
like you ar eusing English standards and the data my of originated in the US. You also may have a single quote in front of the string which is forcing TEXT. You can remove the single quotes by using worksheet REPLACE (menu edit Replace) and in the from box put in a single quote and put in the to box nothing. Using the datevalue function may solve the problem =DATEVALUE(I3) Yo also may need to us a formula to extract the year month and day. something like this =date(left(I3,4),mid(I3,6,2),right(I3,2)) "Eduardo" wrote: Hi Lynda, What do you get, try doing text to columns for that imported dates or in an auxiliar column enter =Trim(A1) to trim any blank space then copy and paste values and format it see what happens Hope this helps "Lynda" wrote: I have looked through other threads but can't find an answer to my query. I have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda |
#4
|
|||
|
|||
Formatting Dates
Thank you Eduardo, I will try this when I get back to work tomorrow and let
you know how I got on. Cheers Lynda "Eduardo" wrote: Hi Lynda, What do you get, try doing text to columns for that imported dates or in an auxiliar column enter =Trim(A1) to trim any blank space then copy and paste values and format it see what happens Hope this helps "Lynda" wrote: I have looked through other threads but can't find an answer to my query. I have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda |
#5
|
|||
|
|||
Formatting Dates
Hi Joel, (BTW we are using 2003) I did notice when we went into the format
cells that the date format was showing the English (US) so we changed it to English (Australia) but that didn't work either. I will also try your suggestion when I get back to work tomorrow and let you know how I get on. Cheers Lynda "Joel" wrote: You may be having problems with different international standards. It look like you ar eusing English standards and the data my of originated in the US. You also may have a single quote in front of the string which is forcing TEXT. You can remove the single quotes by using worksheet REPLACE (menu edit Replace) and in the from box put in a single quote and put in the to box nothing. Using the datevalue function may solve the problem =DATEVALUE(I3) Yo also may need to us a formula to extract the year month and day. something like this =date(left(I3,4),mid(I3,6,2),right(I3,2)) "Eduardo" wrote: Hi Lynda, What do you get, try doing text to columns for that imported dates or in an auxiliar column enter =Trim(A1) to trim any blank space then copy and paste values and format it see what happens Hope this helps "Lynda" wrote: I have looked through other threads but can't find an answer to my query. I have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda |
#6
|
|||
|
|||
Formatting Dates
On Tue, 18 Aug 2009 05:05:01 -0700, Lynda
wrote: I have looked through other threads but can't find an answer to my query. I have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda If none of them are changing, then they are probably being imported as Text. One solution: Select the column of dates. Select the Text-to-Columns wizard (Data menu) and, when you get to the appropriate step, select YMD as the date format. After you do this, Excel should convert them to "real" Excel dates and you should be able to format them however you wish. --ron |
#7
|
|||
|
|||
Formatting Dates
Eduardo, Joel and Ron, thank you all. Ron, I didn't get to try your
suggestion but I will put it in my 'Computer Tips' folder for future reference. Thank you all once again. Cheers Lynda "Ron Rosenfeld" wrote: On Tue, 18 Aug 2009 05:05:01 -0700, Lynda wrote: I have looked through other threads but can't find an answer to my query. I have dates that I am unable to format. We run an SQL query at work and the dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy but they refuse to change. We tried the normal date format, no good, then we tried the custom format and still no good. Does anyone have any ideas as to how we might be able to fix this? Cheers Lynda If none of them are changing, then they are probably being imported as Text. One solution: Select the column of dates. Select the Text-to-Columns wizard (Data menu) and, when you get to the appropriate step, select YMD as the date format. After you do this, Excel should convert them to "real" Excel dates and you should be able to format them however you wish. --ron |
Thread Tools | |
Display Modes | |
|
|