|Working with Dates in Excel|
|Dates to be Treated as Text||Back
List of Topics
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.
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.
A Potential Trap:
If you perform basic copy
and paste commands in Excel, the text date discussed above will copy and
keep the apostrophe
If this is a problem in your macros, adopt the macro to use the following logic:
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