Working with Dates in Excel
  Dates to be Treated as Text Back to 
List of Topics
 
  Home
Working with dates in Excel is relatively seamless, however, working with dates that you desire to act as text can, at times, be quite frustrating.

This article will present two very common situations where the ease of use in working with dates can sometimes cause the user to become tripped up later in a particular spreadsheet. The first is how to get a date to display beyond the width of a cell, along with with a macro fix. The other is explaining why some cells do not display as expected. Alternative approaches are also presented.

Example 1:

  1. Select an unformatted cell.
  2. Type June 10, 1998 and press ENTER
      3. Note the result. Excel converts the entry to a date value and
      formats the cell accordingly, as you would expect. From a
      very basic point of view, this is a nice feature, as this date value
      can be used in date arithmetic, etc.
       
  3. But what if you desire to enter the date as text?
  4. To do so, press the apostrophe ( ' ) before making the entry, then type the text.
  5. So you would type: 'June 10, 1998 and press ENTER
      6. The date now displays as text.
Why would you want to do this? In many cases, your column widths are set for specific sizes related to your immediate report. Then, let's say, you want the date at the top of the report. All the column widths in your worksheet are set for your report and are, let's say, fairly narrow. If a regular date was entered, the cell would display #########, indicating the cell is not wide enough. Making the cell wider, would throw off the rest of the report. But, by entering it as text, the text that is wider than the cell, will simply hang over and display in the next cell(s).

A Potential Trap:

If you perform basic copy and paste commands in Excel, the text date discussed above will copy and keep the apostrophe ( ' ). However, when copying and pasting in macros, the apostrophe is dropped by the macro and the date is entered as date value.

If this is a problem in your macros, adopt the macro to use the following logic:

    Sub CopyDate ()
      vDate = Range("Date.Entered")
      Range("Date.Entered") = " ' "vDate
       
    End Sub
The first step in the macro places the contents of the range named Date.Entered into a variable.

The second step adds the apostrophe ( ' ) in front of the date and then places it back into the same cell.

This specific macro works in a particular spreadsheet project and shows the logic needed to solve the problem. Obviously, this can be edited to fit your particular circumstance and macro writing style.

Note: Excel's macro language, VBA, has several different methods of copying, incurring the same basic result. This particular copy macro, converts the date text to a date value, however, other methods of copying may not. If your working under pressure to get a project done, and you don't have the time to investigate other methods, this is a good solution. However, you may learn over time that other methods of copying (in macros) produce more desirable results. The more your work with the program, the more you learn.

Another solution to the date being wider than the cell is to format this cell and the cell(s) to its immediate right to "Merge and Center".  Let's say your date is in cell G5 and will not display as the cell is too narrow.  Highlight cells G5:H5 and click on the Merge and Center tool in the toolbar.  This command centers combines the two cells into one. If need be, highlight a third or fourth cell, say G5:I5 or G5:J5 merging more cells. At first glance, this method seems to be much more preferable than converting the date text by adding the apostrophe ( ' ). But be careful, merged cells place other limits on the spreadsheet, for example, you cannot insert a column within columns that contain merged cells.

     


 
 
Top

 
 
 
 
 
 
 
 
 
 
 
 

 

solutions@info-stat.com