Working with Dates in Excel
 
  Formatting Issues Back to List of Topics
   
  Home
Sometime a user may enter a number, say 35,345.56, and upon pressing Enter
10/17/96 displays in the cell. Inexperienced users may become very frustrated and not be able to get the number to display as desired.

Example:

  1. Select an unformatted cell.

  2.  
  3. Type 7/15/98 and press ENTER
    1. Note the result. Excel converts the entry to a date value and formats the cell accordingly, as you would expect.
  4. Press Delete (on the keyboard)
    1. This deletes the contents (but not the formatting) of cell. Now, pretend you have worked on this spreadsheet for a few days and now come back to this cell to make an entry
  5. Type 35679 and press Enter
    1. Note the displayed results. Your entry of 35679 displays as a date. Why?
When you enter a date as in Step 2, Excel interprets your entry and formats the cell on the fly. Thus, the cell is now formatted for dates and will retain that format until it is changed or deleted.

In this example, we pressed Delete. Pressing Delete is the keyboard shortcut for Selecting Edit (in the menu) Clear Contents; deleting the contents of the cell, leaving the formatting in place.

Therefore, when you later came back to this cell to make a regular number entry, Excel simply used the format that was already there.

There are several simple ways to solve the problem, two of which follow:

  1. When erasing a date, instead of pressing Delete, select Edit Clear All (from the menu).

  2.  
  3. When making the new non-date entry and displays in the date format, select Format Cells Number (in the menu) and select the desired format (or select the Comma format from the formatting toolbar).

  4.  

     
     
     
     
     

    Reminder: Cells retain their format until changed. When you make entries such as dates, you formatted the cell on the fly. If an entry is later made in that cell that is not a date, it will display in the date format anyway.

    Try this: In an unformatted cell, enter $12,784.55 in a cell (enter the dollar sign, comma and decimal along with the numbers). Then press Delete and enter a date such as 9/6/96. You will get the Excel serial number rather than the date. This example is the reverse of the first example in this article, you formatted the cell on the fly by typing the format along with the number.  That cell will hold this format until the format is changed.


 
     
 
Top

 
 
 
 
 
 
 
 
 
 
 
 

 

solutions@info-stat.com