Tip #2000-4: Formatting Dates Correctly with Live Data
Sometimes I get dates in my live data (like
the MATURITY DATE field) but when I use Excel's cell formatting
feature, nothing happens. What can I do?
If you are pulling date fields into the worksheet, be aware that
what looks like a date may not truly be one. If you are
using Reuters' default formats, your dates are coming in to
the spreadsheet as text, not dates. Show
me a sample sheet (Reuters users: download a sample sheet)
To get a true date you have to:
- Pull in the data using Reuters' optional
format field
- Format the cell as usual to show the type
of date you want
Reuters provides an optional format
field in every DDE formula. If you enter or paste a DDE formula
in an individual cell but don't enter a number in the optional format
field, data conversion is performed automatically by Excel. Once
data conversion is completed, numeric data, such as prices, are
returned with five decimal places; text and non-price data, such
as dates, have a leading space inserted; and special characters
for up and down tick arrows have been converted to "U"
and "D." If you just want to display
the data, the data conversion results are usually acceptable. However,
in cases where you need to customize the format or want to use the
MATURITY DATE field in a calculation, you should avoid using Excel's
automatic conversion.
When you use the optional format field provided by Reuters, data
can be displayed in an Excel worksheet cell exactly
as it is received from the Reuters network. The easiest way
to set up your MATURITY DATE field to format properly is to
add the number "1" to the remote reference formula.
For example change the formula from:
| =REUTER|IDN!'T5D01,MATURITY DATE' |
to:
| =REUTER|IDN!'T5D01,MATURITY DATE,1' |
Once you change the formula, the value in the cell will change from
01-APR-01 to a serial number (the number of days elapsed since
January 1, 1900). Show me
If you choose Cells... from the Format
menu, click the Number tab, and then choose
Date, you can apply any of the available date
formats. Show me
The date that appears in the MATURITY DATE field is now an actual
date, not text. Not only is the date formatted
correctly, you can now use the value in the MATURITY DATE field
in a calculation. For example, you can now sort the database
chronologically using the values in the MATURITY DATE field.
|
