Tuesday, September 27, 2011

How to Convert a Number to a Date in Excel 2003 When the Format Is yyyymmdd


1. Create a grid with your numbers in Excel, if you haven't already done so. Open a new spreadsheet and type your numbers into column A. Leave column B blank – it will be used momentarily for the data calculation.
2. Type the following formula into column B:=DATEVALUE(MID(A2,5,2)'/'RIGHT(A2,2)'/'LEFT(A2,4))This sets up the formula to convert your numbers in column A to a date format, and the result will be displayed in column B. The output won't actually look like a date until you format the cell. Continue this formula for as many rows as you have data. You do this by moving your mouse to the corner until the cursor changes to a cross; then, click and drag down the number of rows required.
3. Highlight the cells in column B that you want to convert to a date. Select 'Format' and then 'Cells.' Select 'Date' in the Format Cells box. Choose any date format desired. Readable dates instantly populate in column B of your spreadsheet.

Blogger news