Establish what the number represents

Excel can store dates as serial numbers. Microsoft illustrates 5 July 2011 as 40729 in the 1900 date system. A number in a date column therefore does not necessarily mean the date was lost. However, not every number represents a date. Check the column's purpose and an original record before treating an unfamiliar value as a calendar entry. [2]

Save a copy and select only one or two cells in the intended date column. Leave order numbers, names and amounts outside the selection. Use a row whose real date you know, and record its current display alongside the source. Making a value look like a date and confirming that it is the correct date are separate checks.

Apply a date display to a known value

In Windows desktop Excel, select the cells and press Ctrl+1 to open Format Cells. Microsoft describes choosing Date on the Number tab, selecting a format and checking the Sample preview before applying it. Choose a display that includes the information you need, then test one known cell before formatting the entire date range. The instructions here concern display, not replacing the original entry. [1]

Regional settings can change the default order, so consider whether a display such as 03/04 is clear to the recipient. Choose an available format that makes the year, month and day understandable. Do not overwrite an unknown number with a guessed date merely to make the sheet look tidy. Once a known example matches its source, extend the formatting only to the actual date cells.

Check column width when you see hashes

If date formatting produces #####, the value is not necessarily missing. Microsoft explains that the column may be too narrow and suggests double-clicking its right border or dragging the edge to widen it. Check whether the full date becomes visible before changing the underlying entry. This separates a shortage of display space from a problem with the data. [1]

A serial number and a row of hashes are different symptoms. If the format is appropriate but the column is narrow, retyping dates is unnecessary. After resizing, inspect neighbouring rows and the heading. For a document you will submit, also check the print or PDF layout. Avoid deleting the column's contents or discarding part of the date simply to make the visible width fit.

Investigate imported entries that do not respond

A copied table or imported file may contain text that resembles dates. If choosing Date does not give the expected result, check the source program and intended date order before attempting a whole-column conversion. Without the source, 03/04 could be ambiguous. A plausible appearance is not enough to confirm the meaning of an imported entry or the correctness of a conversion.

Confirm whether the original number is a date serial or an identifier such as an order code. Ask the data provider for a known example if necessary. Any separate conversion should be tried on a small copied range and compared with the source. Do not treat a display-format adjustment as a universal repair for text dates; retain unresolved cells for investigation rather than guessing their intended values.

Compare date systems when moving between workbooks

Microsoft documents the 1900 and 1904 date systems and a difference of 1,462 days between their serial values for the same date. If dates shift by roughly four years after copying between workbooks, distinguish that issue from formatting alone. Do not toggle the entire workbook's date system without checking the source, destination and effect on the other dates. [2]

Keep both files and compare several dates you can verify. Record whether the problem concerns only display or an actual change in the calendar date, then inspect the copying route and workbook settings as needed. Finish by checking readability, agreement with the source and preservation of unrelated number columns. Apply only the changes whose results you have established in the copy.