How would you manage these dates?

[JMF]

I'm back, after posting just a couple of hours ago about "old" dates and
getting great help.

Now I know more about what my friend is trying to do, and I'm intrigued by
the problem and am wondering how a real clever expert, like in this group,
would handle it.

She's archiving historical letters and using an Excel file. So she's got
rows with the topic, who wrote it, who received it, the date, etc. Let's
ignore the problem of "old" dates now and assume they're all after 1900.

Here's the tricky part: she's not sure about some dates.

This is classical, of course, in archival. For example:

1933? means "we think it was written in 1933 but we're not sure"

193? means "we know it was in the 1930s, but we're not sure which
year"

10/09/1934? means "we think it was that date but we're not sure"

Now, of course, you can imagine what happens when you try to put those as
dates into Excel.

Yet, she would still like to be able to sort by dates, etc. so somehow it
would be nice, and important, to preserve the "date-ness" of those date
entries.

So what to do?

It seems to me that there's no way around having more than one item per
date: The date itself, whereby you use some system to "guess" the date where
there is a question mark, and somehow some other item that records the
uncertainty in some way -- I don't know, like another column where it says
"sure/unsure" (although that isn't general enough).

Anybody have any insights? Perhaps one of you has dealt with something
similar?

Thanks,

John

 

[colin]

Hi,

I think you are looking at this the right way....You Need Three Columns
Decade,Year,Date

these columns can then be filtered

decade, No Year, No Date
Decade,Year, No date
Decade, Year, date

The way to populate the fields would be to filter based on the original
column containing "?" and it should be fairly easy to determine if it is
decade or year that you need to populate.

If you are taking thousands of records then you need to look at the LEN
worksheet function for determining if the year is three or four characters.

Hope that this is of use

Rgds

Colin

 

[Ron P]

I'm back, after posting just a couple of hours ago about "old" dates and
getting great help.

Now I know more about what my friend is trying to do, and I'm intrigued by
the problem and am wondering how a real clever expert, like in this group,
would handle it.

She's archiving historical letters and using an Excel file. So she's got
rows with the topic, who wrote it, who received it, the date, etc. Let's
ignore the problem of "old" dates now and assume they're all after 1900.

Here's the tricky part: she's not sure about some dates.

This is classical, of course, in archival. For example:

1933? means "we think it was written in 1933 but we're not sure"

193? means "we know it was in the 1930s, but we're not sure which
year"

10/09/1934? means "we think it was that date but we're not sure"

Now, of course, you can imagine what happens when you try to put those as
dates into Excel.

Yet, she would still like to be able to sort by dates, etc. so somehow it
would be nice, and important, to preserve the "date-ness" of those date
entries.

So what to do?

It seems to me that there's no way around having more than one item per
date: The date itself, whereby you use some system to "guess" the date
where there is a question mark, and somehow some other item that records
the uncertainty in some way -- I don't know, like another column where it
says "sure/unsure" (although that isn't general enough).

Anybody have any insights? Perhaps one of you has dealt with something
similar?

Thanks,

John

You could use a helper column alongside the dates and have codes in it such
as "verified" , "unverified". "approx year", "decade" etc to suit your
situation.