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
|
|||
|
|||
Date Formats
I have a spreadsheet sent to me and it has a column of dates in the following
format - mm/dd/yy I would like to change them to yyyy/dd/mm but I can't seem to change existing format. Any ideas on how this is done? |
#2
|
|||
|
|||
Date Formats
Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function.
If they a =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2)) and format as required -- Kind regards, Niek Otten Microsoft MVP - Excel "Greenback" wrote in message ... |I have a spreadsheet sent to me and it has a column of dates in the following | format - mm/dd/yy | I would like to change them to yyyy/dd/mm but I can't seem to change | existing format. Any ideas on how this is done? |
#3
|
|||
|
|||
Date Formats
Niek,thanks for the reply, but I am unsure of what you mean (not an expert on
excel at all)...can you clarify?...thanks "Niek Otten" wrote: Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function. If they a =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2)) and format as required -- Kind regards, Niek Otten Microsoft MVP - Excel "Greenback" wrote in message ... |I have a spreadsheet sent to me and it has a column of dates in the following | format - mm/dd/yy | I would like to change them to yyyy/dd/mm but I can't seem to change | existing format. Any ideas on how this is done? |
#4
|
|||
|
|||
Date Formats
Assume your date is in A1. In B1, enter this formula:
=ISTEXT(A1) If you get TRUE as a result, than your date is no real Excel dat, but is text. In that case, enter the formula I suggested. In both cases, FormatCellsNumber tab, choose Date, and choose a format from the list that suits your needs. If no such format is in the list: FormatCellsNumber tab, choose Custom. In the Type box, enter "yyyy/dd/mm" (without the quotes) BTW, what do you see if you format the cell as General? -- Kind regards, Niek Otten Microsoft MVP - Excel "Greenback" wrote in message ... | Niek,thanks for the reply, but I am unsure of what you mean (not an expert on | excel at all)...can you clarify?...thanks | | "Niek Otten" wrote: | | Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function. | If they a | | =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2)) | and format as required | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Greenback" wrote in message ... | |I have a spreadsheet sent to me and it has a column of dates in the following | | format - mm/dd/yy | | I would like to change them to yyyy/dd/mm but I can't seem to change | | existing format. Any ideas on how this is done? | | | |
#5
|
|||
|
|||
Date Formats
Just because data looks like dates, doesn't make them dates.
If you tried changing the format and the display didn't change, then Niek was guessing that your data isn't really a date. (A real date would show the change in format.) Niek suggested putting a formula into another column and creating the date that way from the cell with the "text date". Another way is to select that column of dates and do Data|Text to columns fixed width Don't add any lines and remove any that excel added choose mdy as the date format. This will convert that selection to real dates. Now you should be able to format those cells the way you want. Greenback wrote: Niek,thanks for the reply, but I am unsure of what you mean (not an expert on excel at all)...can you clarify?...thanks "Niek Otten" wrote: Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function. If they a =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2)) and format as required -- Kind regards, Niek Otten Microsoft MVP - Excel "Greenback" wrote in message ... |I have a spreadsheet sent to me and it has a column of dates in the following | format - mm/dd/yy | I would like to change them to yyyy/dd/mm but I can't seem to change | existing format. Any ideas on how this is done? -- Dave Peterson |
#6
|
|||
|
|||
Date Formats
Dave/Nick....many thanks for you tips. I went with the Data/Text to Columns
option and it worked a treat. "Dave Peterson" wrote: Just because data looks like dates, doesn't make them dates. If you tried changing the format and the display didn't change, then Niek was guessing that your data isn't really a date. (A real date would show the change in format.) Niek suggested putting a formula into another column and creating the date that way from the cell with the "text date". Another way is to select that column of dates and do Data|Text to columns fixed width Don't add any lines and remove any that excel added choose mdy as the date format. This will convert that selection to real dates. Now you should be able to format those cells the way you want. Greenback wrote: Niek,thanks for the reply, but I am unsure of what you mean (not an expert on excel at all)...can you clarify?...thanks "Niek Otten" wrote: Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function. If they a =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2)) and format as required -- Kind regards, Niek Otten Microsoft MVP - Excel "Greenback" wrote in message ... |I have a spreadsheet sent to me and it has a column of dates in the following | format - mm/dd/yy | I would like to change them to yyyy/dd/mm but I can't seem to change | existing format. Any ideas on how this is done? -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|