Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Help with converting date formats | Excel Worksheet Functions | |||
pivot table not reading date formats | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions |