How would you manage these dates?

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


0
jfavaro (8)
3/28/2006 12:52:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
567 Views

Similar Articles

[PageSpeed] 54

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

"JMF" wrote:

> 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
> 
> 
> 
0
Colin1845 (22)
3/28/2006 2:08:04 PM
"JMF" <jfavaro@tin.it> wrote in message 
news:uPq%23kZmUGHA.224@TK2MSFTNGP10.phx.gbl...
> 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. 

0
spam8716 (27)
3/28/2006 2:26:51 PM
Reply:

Similar Artilces:

relative date appearance
I am working on a project tracker in which relative dates are calculated. For example, Column C's formula is =B(row #) +7, indicating that the date in Column C is the date in Column B plus seven days. Because this is a project tracker, information will be added to the spreadsheet as new projects come along. If I drag the formula down so that it will be available for future projects, the cells read 1/0/1900. Is there a way I can have the formula in place for future projects but leave the cell in which it resides blank? Thanks. pbtpbtpbt@gmail.com wrote: > I am working on a project tra...

enable date and time stamps across entire database
The company database has time stamp fields but they are not being populated. If they were, they could be used in SmartLists and reports. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defa...

Help on Integration Manager
We recreated our database and we we want to bring in our invoice transactions into the new database. We exported all the invoice transactions to excel via Smartlist and made our corrections. Now am trying to bring in the SOP transactions into the new database using Integration Manager. To my surprise the Header Lines (SOP10100) came in but the Detail Lines (SOP10200) did not come in. One problem we have was that the Line items were too many for the rows available in Excel and so be break them into several sheets but the Header was able to fit into one excel sheet. What can I do? Does...

Text not date
How do I change the settings so that if a number like 1-10 or 1/10 is entered into a cell, it will stay like that rather than turning into 10-Jan for Excel 2007? I know I can add a space before the number (which affect sorting and alignments) or set the cell to text, but is there a way to change the defaults to always prevent this from happening. I rarely enter dates and if I do I would rather change it myself. Also, this can be problematic when doing pasting from one source into Excel while keeping links, etc. intact. Thank you. On Fri, 19 Feb 2010 11:53:01 -0800, Bio...

Determine if a month falls between two dates.
Hello, I have been tasked with trying to do the following Column A - Date column B -Date A will always be earlier than B columns C-N will be labeled months Jan-Dec What I need to do is determine if the date between column A and B includes the given month in column C - N and return a value of X. ie... A B C D E F Jan Feb Mar Apr 1/1/10 3/12/10 X X X blank I can then count the X values. Any help would be greatly...

Dates Windows to Mac
I have recently moved from Window to Mac and discovered that some of the dates in my worksheets are experiencing the effects of the 1900 and 1904 date differences. I have hundreds of these worksheets and some have up to ten thousands dates. So whatever changes I make must be accurate because I will not be able to check them all. I cannot work out how to change the options to correct the problem. Help would be greatly appreciated. -- MichaelQ changes made are workbook specific. Open (and Save) a test workbook, then goto Excel>Preferences>Calculation icon and you will see...

Collections Management and Advanced Security
When I'm in Advanced Security it seems that Collections Management Objects are repeated endlessly. This is leading to many thousands of extra entries in the Accelerator Table (and of course many more minutes of Accelerator Processing). I have 20 Companies, 95 Classes and 135 Users. There's no hope of getting a client with ALL of the products and modifications installed on it, so don't tell me to hit the Verify button. The function of File >>Maintenance >> Advenced Security >> Checklinks is unclear. Either one of these solutions seems to threaten data ...

Format of date in my code
I have a subform with a date called txtmontha with a format mmmm/yyyy There is also a command button that when clicked should open another subform which has a date txtmonthlabela, also format as mmm/yyyy. The command button should open the sub form for all records when the value of txtmontha and txtmonthlabela are the same. However when I click on the command button although the form opens I get a prompt box asking me for the value of txtmonthlabela. If I cancel the prompt box the form opens with a number of records shown in the record selector box at the foot of the form but no ...

sheet with expiration dates
I need to set up a sheet that will show how many day's i have left before a contract will expire. One way: A1: <expiration date> A2: =TODAY()-A1 Format A2 as General. In article <A091A457-3A0A-4072-BD7E-9801D3921302@microsoft.com>, "expiration dates" <expiration dates@discussions.microsoft.com> wrote: > I need to set up a sheet that will show how many day's i have left before a > contract will expire. ...

disable macro/code after date & time
I have a code/macro that runs great. After July 1, 2010, I no longer want it to run (even if called). Any code that I can insert? Or better yet. On workbook open.... close in 10 seconds if after date x. If too complicated, then disable calculations after date x. Chip Pearson's site has a section on this topic that should help you http://www.cpearson.com/excel/workbooktimebomb.aspx -- If this helps, please remember to click yes. "J.W. Aldridge" wrote: > > > Or better yet. On workbook open.... close in 10 seconds if after date > x...

All Accounts in Portfolio Manager
The flexibility of Portfolio Manager, with the nice view and all the user definable columns is great – especially the way it automatically averages the total return on all your investments in real time. Because of this total return feature, I’d also like to have my cash savings account listed in Portfolio Manager which I see is not supported if you define your cash in a savings or checking account. Therefore, what is the best way to define a cash account so it will appear in Portfolio Manager, such as setting it up as another investment type in an investment account? Also, what would ...

Preventing auto date format
When I copied a web content to spreadsheet, how to prevent excel from auto convert the paste value to date format? as i know,excel can not auto adjust the copy content to the define format,while u can use vba to attain the goal. i.e. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Font.Name = "courier new" .Interior.ColorIndex = 24 End With HTH Regards Sebation.G "crapit" <biggercrap@yahoo.com> д����Ϣ����:ebDS8RF%23HHA.5980@TK2MSFTNGP04.phx.gbl... > When I copied a web content to spreadsheet, how to prevent excel from auto > convert th...

Dates, Conversions, and Histograms Questions
Some questions. 1. if A1 = 1/5/2005 (date format), is there a function that converts A1 to the day of the year, 5? 2. Suppose the date conversion in 1 is dayofyear(A1). I have a whole column of dates, how can I easily produce a column of =dayofyear(a1) =dayofyear(a2) .... =dayofyear(a20) 3. Suppose I want a count of all the occurrences of events in a week and want to produce a histogram for a 20 week period, is there a way to do it? For example, assuming 1/1/2008 is a Monday, and is the considered the first day of the week, I have this data. Date 1/4/2008 1/6/2008 1/6/2008 1/6/2008 1/7/2...

Managing Event in CRM System
What is the best practive in managing event in CRM system? So that it will be easier next time we want to keep tract of those information? what function should i use in that system? kindly need guidance. Tq If you mean managing event attendance, such as conferences, it depends on your requirements. You may need an ISV add-on product that extends MSCRM to handle this. There are several available, including from www.axonom.com . I have not researched others recently HTH Peter "Zaqry Omar" <ZaqryOmar@discussions.microsoft.com> wrote in message news:35D7E119-7333-46AA-...

ISO SQL Date Format / ISO Date Formats
ISO date formats come in a number of variations, two such variations are YYYY-MM-DD and YYYYMMDD. The ISO SQL Standard date format is YYYY-MM-DD only which is termed a "date literal". According to the standard (see here: http://savage.net.au/SQL/sql-92.bnf.html#date literal) the "date literal" in order for it to be a "date literal" MUST be prefixed by the keyword DATE, if not then the text is simply nothing more than a "string literal" which has different meaning and subject to regional settings if used on data type conversions ie. st...

Sort these notes by date
Is there any way to sort these notes by dates? If I do a search by my name they come back in random order. It would be most helpful if I could sort by date so I can see the most recent ones first. And by "these notes" you are referring to? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Rich Wheeler" wrote: > Is there any way to sort these notes by dates? If I do a search by my name > they come back in random...

Shortcut key in register by DATE?
In Quicken, I could hit Ctrl-D and automatically go to a specific date in my register. How can that be done in Money 2006? If Ctrl+D doesn't so it--and I've never heard of it--you probably don't do it in M06. "iomag" <iomag@discussions.microsoft.com> wrote in message news:BCEA6349-7BE4-408D-8D8C-F65151AFE2BA@microsoft.com... > In Quicken, I could hit Ctrl-D and automatically go to a specific date in > my > register. How can that be done in Money 2006? "Dick Watson" <littlegreengecko@mind-enufalready-spring.com> wrote in message ...

Y axis data point alignment with x axis dates
I have a graph with a Y axis that plots a population number ending each month for approximately two years. Each month the graph grows with the addition of a new month's data. The X axis evenly and correctly displays each month across the bottom of the graph. The problem is that many of the data points on the Y axis do not align with the corresponding month and fall between months or the ticks along the bottom of the graph area. Some are on the mark, some are a little off, and a few are way off. How do I get the data points to aligh with the month of the X axis along the bottom ...

Business Contact Manager
Hi, I have installed the Office 2003 Beta 2, and I also installed the Business Contact Manager, it work for a while,but just as I was get the hang of it it disappeared, so I tried to re-install it but nothing, I even de-installed it completely and re-installed it from the CD-ROM and then I went on the net to D/L the technical refresh, but I still can't use it in Outlook 2003 anymore All I did was to install a Windows Update for the system and the I needed to reboot so I did and on reboot BCM was gone I really would like to get it back, so if yo -- Thank you in Advance Merci a l'A...

Business Contact Manager
Can you guide me to the Forum for Business Contact Manager? I upgraded from Office Suite 2007 incl BCM07 to Office Suite 2010. The only BCM 2010 is beta. I'm having difficulties installing the Beta version with the release versions of Office Suite 2007 (visio, project, publisher, and basic Office Pro) Thanks, Rick Pelham -- "RSVP" <RSVP@discussions.microsoft.com> wrote in message news:F62D8488-EA90-417F-A90F-FA4A34F1DCFA@microsoft.com... > Can you guide me to the Forum for Business Contact Manager? > > I upgraded from Office Suite ...

Adding central key management for signing and encrypting mails
I want to have exchange manage keys on the server so that users are able to sign and encrypt their emails and also create rules for automatically encrypting email that are targetted at specific receipients or companies. This has to be working transparently even with OWA. No ActiveX control that has to be downloaded on every client must be used. Does anyone here know if there is some kind of addin available for Exchange, a third party application or even a microsoft in-house solution for that? I am looking forward to your answers. Best regards, T. You can use MS Certificate Services wh...

Is it possible to add dates in an excel worksheet to Outlook Cale.
I would like to be able to select a cell with a date in & copy the related info, eg: domain name renewal date to Microsoft Outlook Calendar by using a tool within excel, rather than switching between excel & Outlook - is there a function/tool that allows this, eg Smart Tag??? ...

Look for Oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

I need a date line
I have a chart that is a bar chart. I have inserted a dotted line with an approximate date. I would like this line to move according to today's date. Can someone help me? -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:CCB98FD8-A584-42F5-A66A-02ACE9EDAB13@microsoft.com... >I have a chart that is a bar chart. I have inserted a dotted line...

cleared date on check inquiry
Why doesn't the system show a date cleared on any inquiry window in GP? It would be very helpful to lookup a payables check and be able to see if the check has cleared the bank and in what month - or even if it has been marked as cleared. It shows a void date if voided, but we have to search through bank rec and the bank's online system. This is very time consuming. I know the information is in two tables PM and CM, however, so is the void information and it writes back and forth, so it would seem feasible that the cleared status could also write to both tables. -------------...