In Excel, how can you format for 4 digit years (MM/DD/YYYY)?
I'm pulling in an unformatted 8-digit date field from an AS400 into an excel
spreadsheet. I would like to be able to format it in MM/DD/YYYY format in the spreadsheet, but get weird numbers when I do so. I don't see anything in the standard date formatting section and have created a Custom Format as ' / / ', but this will not work either. |
Chucky,
You'll need to use the date(year,month,day) function to convert to an excel date. You can break down your data from the AS400 by using string functions. Say the date is 02042005 in cell C7, then the formula would be =DATE(RIGHT(C7,4),LEFT(C7,2),MID(C7,3,2)). You can then format it as you wish. Gary "Chucky" wrote: I'm pulling in an unformatted 8-digit date field from an AS400 into an excel spreadsheet. I would like to be able to format it in MM/DD/YYYY format in the spreadsheet, but get weird numbers when I do so. I don't see anything in the standard date formatting section and have created a Custom Format as ' / / ', but this will not work either. |
Chucky
Assuming your data is coming in like 20051231 or similar. Try DataText to ColumnsNextNextColumn Data FormatDate and pick the appropriate format then Finish. You will have to then format the cells to your MM/DD/YYYY format. Gord Dibben Excel MVP On Wed, 19 Jan 2005 13:17:23 -0800, Chucky wrote: I'm pulling in an unformatted 8-digit date field from an AS400 into an excel spreadsheet. I would like to be able to format it in MM/DD/YYYY format in the spreadsheet, but get weird numbers when I do so. I don't see anything in the standard date formatting section and have created a Custom Format as ' / / ', but this will not work either. |
Select the import, do datatext to columns, click next twice, select Date
under columns data format and from dropdown select the imported date format so if it can look like 10122004 (10/12/2004) then select MDY and click finish Regards, Peo Sjoblom "Chucky" wrote: I'm pulling in an unformatted 8-digit date field from an AS400 into an excel spreadsheet. I would like to be able to format it in MM/DD/YYYY format in the spreadsheet, but get weird numbers when I do so. I don't see anything in the standard date formatting section and have created a Custom Format as ' / / ', but this will not work either. |
Good tip Peo, I learned something new.
Gary "Peo Sjoblom" wrote: Select the import, do datatext to columns, click next twice, select Date under columns data format and from dropdown select the imported date format so if it can look like 10122004 (10/12/2004) then select MDY and click finish Regards, Peo Sjoblom "Chucky" wrote: I'm pulling in an unformatted 8-digit date field from an AS400 into an excel spreadsheet. I would like to be able to format it in MM/DD/YYYY format in the spreadsheet, but get weird numbers when I do so. I don't see anything in the standard date formatting section and have created a Custom Format as ' / / ', but this will not work either. |
All times are GMT +1. The time now is 01:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com