Problem Date formats (days360): non visible characters?

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

This should be easy, but I'm not making headway.

I have two dates in a row of a spreadsheet. I want to
count the days between them. The two dates come from an
extract of an incident tracking system. Currently, the
extract process consists of cutting and pasting from HTML
directly to the spreadsheet. I mention this because both
dates seem to be followed by a single space(" ") and may
have some other invisible junk in there.

So, let's say the dates come in looking like "2/4/2004 "
and "3/14/2004 ". First, (Question 1) I'd like to know
how to confirm that these are text fields rather than
date fields. Apparently, Excel masks this info, allowing
the user to format both text dates and real dates using
Format->Cells->Date.

I've tried to clean up the dates as follows in two new
columns: =value(trim(o32)) and =value(trim(p32)). This
gives me errors, though when I manually put the text into
the formulas myself, ie =value(trim("2/4/2004 ")) and
=value(trim("3/14/2004")), I get results that look okay.
Again, I don't know how to confirm that the value is
really a date, but it must be because using days360() on
these new fields works.

Hence Question 2 what hidden junk must be in the cells
coming from the incident tracker which I can't see. Is
there a way to see the unformatted contects of a cell?

-Bill
 
Bill,

simplest method, try adding 1 to the date.

The key to your issue is the method Excel uses to store
dates. There are two date formats a) 1/1/1900 and b)
1/1/1904. Most systems use 1/1/1900 and call this day 1,
with 2nd Jan, 1900 as day two and 37987 as 1/1/2004.

Excel stores dates as numbers and times as decimals (06:00
is.25, noon as .5, 6pm as .75).

Therefore to count the days between two dates, merely
subtract the earlier from the later.

There are a number of functions that you can use to
determine the data type within a field. These include:
istext
isnumber
islogical
iserror
isna

The data import is where you can check the data being
imported. This will depend on the method used. If you are
using the file open command to open a txt file, you can
see the data being imported, and can dictate the usage and
format of the import. If the data is being read in using
code, then you will need to dive into the code and perhaps
use debug.print to check the individual cell data.

Steve
 
Back
Top