Hand entered dates view wrong

A user I support is having an issue with a worksheet in Excel.  It is 
not an urgent or crucial fix, but I am interested in people's ideas, 
since she says it has happened to her before. In each case she threw 
away the worksheet and started a new one by hand entering the data again.

Here's the scenario: She has a worksheet in which she has been adding 
rows of data over time (6 months or so) and has only about 30 rows of 
data.  Column A are dates, and are formatted as such.

After months of opening and saving in this document once a week or so, 
she now has an issue - when she types in a date, it gives her bizarre 
dates from the future.  For example, when she enters 8/01/05 by hand, 
the date displayed in the cell is 4/26/2119.

Things I know:

   The 1904 date system is not checked in Tools/Options/Calculations

   The worksheet works fine on my computer when she emails it to me

   We have cleared the formatting on the cell, and the entire column

   The local system time is correct



Has anyone seen such issues?

- Chris in Nebraska
0
cpultz2 (5)
8/2/2005 4:27:28 PM
excel 39879 articles. 2 followers. Follow

6 Replies
505 Views

Similar Articles

[PageSpeed] 22

What happens if she uses the format painter and copies the format from a
good formatted cell to these cells?
http://www.officearticles.com/misc/format_painter_in_microsoft_office.htm
*******************
~Anne Troy

www.OfficeArticles.com


"Chris in Nebraska" <cpultz2@gmail.com> wrote in message
news:eSsvT83lFHA.2156@TK2MSFTNGP14.phx.gbl...
> A user I support is having an issue with a worksheet in Excel.  It is
> not an urgent or crucial fix, but I am interested in people's ideas,
> since she says it has happened to her before. In each case she threw
> away the worksheet and started a new one by hand entering the data again.
>
> Here's the scenario: She has a worksheet in which she has been adding
> rows of data over time (6 months or so) and has only about 30 rows of
> data.  Column A are dates, and are formatted as such.
>
> After months of opening and saving in this document once a week or so,
> she now has an issue - when she types in a date, it gives her bizarre
> dates from the future.  For example, when she enters 8/01/05 by hand,
> the date displayed in the cell is 4/26/2119.
>
> Things I know:
>
>    The 1904 date system is not checked in Tools/Options/Calculations
>
>    The worksheet works fine on my computer when she emails it to me
>
>    We have cleared the formatting on the cell, and the entire column
>
>    The local system time is correct
>
>
>
> Has anyone seen such issues?
>
> - Chris in Nebraska


0
ng1 (1444)
8/2/2005 5:38:05 PM
Chris,

I wonder if we looked at the underlying value, might we get a clue.  Try 
removing the date formatting (Edit - Clear - Formats will do it -- you can 
undo it after you've seen the underlying date serial number).
--
Earl Kiosterud
www.smokeylake.com

"Chris in Nebraska" <cpultz2@gmail.com> wrote in message 
news:eSsvT83lFHA.2156@TK2MSFTNGP14.phx.gbl...
>A user I support is having an issue with a worksheet in Excel.  It is not 
>an urgent or crucial fix, but I am interested in people's ideas, since she 
>says it has happened to her before. In each case she threw away the 
>worksheet and started a new one by hand entering the data again.
>
> Here's the scenario: She has a worksheet in which she has been adding rows 
> of data over time (6 months or so) and has only about 30 rows of data. 
> Column A are dates, and are formatted as such.
>
> After months of opening and saving in this document once a week or so, she 
> now has an issue - when she types in a date, it gives her bizarre dates 
> from the future.  For example, when she enters 8/01/05 by hand, the date 
> displayed in the cell is 4/26/2119.
>
> Things I know:
>
>   The 1904 date system is not checked in Tools/Options/Calculations
>
>   The worksheet works fine on my computer when she emails it to me
>
>   We have cleared the formatting on the cell, and the entire column
>
>   The local system time is correct
>
>
>
> Has anyone seen such issues?
>
> - Chris in Nebraska 


0
someone798 (944)
8/2/2005 5:46:58 PM
Might not help, but try comparing the date formats in her regional
settings with yours. Control Panel>Regional Options, click customize,
choose Date tab, see what the setting is for dates entered as two digit
years. I say this because you indicate the year is entered as two
digits, poor practice for MS Office ever since Y2K. Regardless of the
numeric (not text) date format, years should be entered as four digits
(i.e. even if format is mm/dd/yy, enter 08/03/2005). She should try her
sheet with four digit years if she hasn't already done so.
If none of that gives you a clue, change the format in her spreadsheet
on her machine and on yours to General. The number you get on each for
a given date should be the same (08/01/2005 is 38565). If you don't get
the same result, and the system clocks are correct on both, I have to
suspect the regional settings are the cause.

0
micron (18)
8/3/2005 4:41:07 AM
On Tue, 02 Aug 2005 11:27:28 -0500, Chris in Nebraska <cpultz2@gmail.com>
wrote:

>After months of opening and saving in this document once a week or so, 
>she now has an issue - when she types in a date, it gives her bizarre 
>dates from the future.  For example, when she enters 8/01/05 by hand, 
>the date displayed in the cell is 4/26/2119.

It seems to me she is forgetting to put in the date separators.

The serial number 080105, when entered into a date formatted cell, will display
4/26/2119.

Another option is that someone programmed her auto correct options to eliminate
the "/" but that's less likely.


--ron
0
ronrosenfeld (3122)
8/3/2005 11:47:39 AM
A-HA!!  I knew someone would have the answer!

Many thanks Ron - I will let her know!





Ron Rosenfeld wrote:
> It seems to me she is forgetting to put in the date separators.
> 
> The serial number 080105, when entered into a date formatted cell, will display
> 4/26/2119.
> 
> Another option is that someone programmed her auto correct options to eliminate
> the "/" but that's less likely.
> 
> 
> --ron
0
cpultz2 (5)
8/3/2005 6:58:16 PM
On Wed, 03 Aug 2005 13:58:16 -0500, Chris in Nebraska <cpultz2@gmail.com>
wrote:

>A-HA!!  I knew someone would have the answer!
>
>Many thanks Ron - I will let her know!

You're welcome.  Hopefully the answer is correct :-|


--ron
0
ronrosenfeld (3122)
8/3/2005 10:46:57 PM
Reply:

Similar Artilces:

Date construct with DatePart is kicking my.... well, you know.
Private Sub cmdCreatePath_Click() Dim strDateCons As String <bunch of my code that is (surprisingly) working like it should> strDateCons = DatePart(yyyy, Me.txtDateEntered) & "-" & DatePart(mm, Me.txtDateEntered) & _ "-" & DatePart(dd, Me.txtDateEntered) Debug.Print strDateCons End Sub When I click the magic button... nuffin'! I get an error message that states "Compile error: Variable not defined" and it highlights the 'yyyy' part of my first DatePart statement. What I actually wanted it to do was to take the date ...

How does one view a document created in Publisher.
I have gotten a document (calendar) in .pub format. Word does not recognize it. Is there a viewer? What's the link? I have Office 2000 with the converters installed. Collins wrote: >I have gotten a document (calendar) in .pub format. Word > does not recognize it. Is there a viewer? What's the > link? I have Office 2000 with the converters installed. ==================================== There is no viewer for Publisher files. Maybe you could ask the sender to convert it to a .pdf file. Or you could order the Trial version of Pub2003... which will still function as a viewer a...

How to correct wrong investment type in Money2004
In my hast I incorrectly labeled one of my mutual funds as a money market investment type. How can I correct this fund back to a mutual fund type? "Details" of the fund shows the investment type as Money Market but offers no apparent way to change it. Thanks. Ed See http://www.bollar.org/msmoney/#Q81. "Ed Haynes" <anonymous@discussions.microsoft.com> wrote in message news:6c5f01c3e614$8593b490$7d02280a@phx.gbl... > In my hast I incorrectly labeled one of my mutual funds > as a money market investment type. How can I correct > this fund back to a mut...

What is wrong with this line?
mySeries[1] = mySeriesCollection.Add(myWorksheet.get_Range("D2", "D13"), Excel.XlRowCol.xlColumns, false, false, false); Causes this error... System.InvalidCastException was unhandled OleAut reported a type mismatch. What am I doing wrong? ...

Changing a text field to a date field
Hi I know this is a problem for lots of people but I still cant find an answer. I have imported a text file into excel with a number field that displays credit card expiry dates as a 4 digit number. example 0408 so month + year. I have converted the number field to a text field so it doesnt drop off the first zero and now I need to convert it to a date field. Any nice easy simple ideas. Any help would be much appreaciated. Or direction to a question the same. Perhaps =DATE(100+RIGHT(A1,2),LEFT(A1,2),1) format as date -- daddylonglegs --------------------------------------------...

Date and time onto a form
Hi, I am designing a form that my client would like the date and time automatically recorded in a field and stored so that when they refer back it shows when the record was done. The 'NOW' function only shows the current date & time and keeps updating. Is there a way of putting the 'NOW' value into a field, then locking it so it doesn't change when the record is reopened. Hope that makes sense! Kazlou The form stores data in a table. You need to add a date/time field to your table to record the date and time a record was last updated. 1. Open the table in desig...

Forms in two differant views
Is there a way to use the same sub form showing two differant views within the same Main Form. For example: Can I show on one tab a datasheet view and on the other a regular form view. -- Rose Hi Rose, Create a button on your form and under the "OnClick" event put 'Me.DefaultView = Datasheet Alternatively you could use a checkbox If me.checkbox1 = 0 then Me.DefaultView = 2 ' Datasheet Else Me.DefaultView = 0 'Single Form End if me.repaint This should flick the form between datasheet and form views. HTH, Nick. "Rose" wrote: > Is there a way to use t...

date #4
Hi All I live in South Africa, and I have an interesting problem that persists in Excel 2003 and 2007. =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006 =month(now()) returns 12 =text(month(now()),"MMMM") returns January =year(now()) returns 2006 =text(year(now()),"yyyy") returns 1905 Why would this be? Thanks Chris Chris, =text(month(now()),"MMMM") should be =text(now(),"MMMM") and =text(year(now()),"yyyy") should be =text(now(),"yyyy") or just =year(now()) Otherwise, you are calcing the month of the 12th day afte...

Date question 01-14-10
Can anyone help with this function: =IF[Date Parts Ordered Completed]+ [Date Part Completed]+[Date Purchasing Completed],not blank,=Date The goal is that is all three fields have a date in them, then put in today's date in the final field. If one of the dates is missing, then the final date is blank. The only problem is if I open up that form I do not want it to change the date to today. I want it to stay the date of the actual completion. Any help is greatly appreciated. Thanks, P-Chu -- Message posted via http://www.accessmonster.com Try this -- Completion_...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

Infopath w/ manually entered values in drop-down and qry results
I had originally posted this elsewhere, but was told this forum is the appropariate plase. I have an Infopath form with a drop-down listbox, that is poulated with manually-entered values. I choose a value, submit the updated data, and it does put the correct value in the SQL 2005 database. However, the next time I query the data, the value in the drop-down list box is the default value for the list-box, not the value from the database, which is misleading. I would have expected the drop-down listbox to display the value from the database instead. Thanks. On Tue, 4 Sep 2007, in...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

GL Posting date is missing or invalid error message
Hello, When trying to post a Sales order through Sales transactions entry window, we get an error message 'General ledger posting date is invalid or missing' The dates seem correct. The fiscal periods are all set up correctly. This happens whether we try to post an individual transcation or a batch. The batch looks like it is posting, but all the posting reports have zero mamounts in them & the batch still sits there even after posting as if it has never been posted. We are running GP 8.00g34 What is causing this & how do we fix it? Thanks Did you recently change your pos...

entering Unicode characters
Hi everybody, I have a MFC app with a dialog with a Rich Edit control. I need to enter Unicode characters. When I enter Alt+nnn, I get the corresponding Unicode character (for example Alt+937 gives the greek letter omega). When I do the same with a normal edit field (not Rich Edit) I get another character (looks like french e) - probably it gets converted to ANSI. I need to be able to enter Unicode characters in the standard edit fields using the Alt+nnn method. How can I achieve that? Can I enable it somehow or do I have to implement it? I am using Windows 2000, VS 6.0. thanks for a...

Changing Exch5.5 GAL columns in Outlook client view
Sorry if the subject line is a bit cryptic; anyway, in the Outlook client, if you display the 'Address Book' you get the following columns - name, business phone, office, title, company, alias, e-mail type, & e-mail address. How do you change it so you show the display name, business, extension, cell phone, & internet email address? Basically, how/where do you change the columnar data the client sees? Thanks, Mike Lawson Mike Goto View | Columns and there u can manage views... KJ "Mike Lawson" wrote: > Sorry if the subject line is a bit cryptic; anyway, i...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

Date Formula #3
I am having difficulty with the "NOW" formula. What I would like for it to do is when I enter a value in one cell have it update in another cell the date when the information was entered and that date stay permanenty. What I am having is the date changes to the current date everyday. Is this possible to do? Any help would be appreciated. Thanks. Todd You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose ...

enter value in a cell from a combo box
How to build a combo box to be able to select a value and enter in the cell 1. Go to View > Toolbars > Control Toolbox 2. Click on the "Combobox" icon once and draw one on your worksheet (hold down the ALT button to fit to a cell). 3. Right-click on the Combobox and go to Properties. 4. In the ListFillRange type in the range of cells that contain the values to fill the combobox, like A1:A5. 5. In the LinkedCell field type a cell reference that will contain the user's selection (ie E2). 6. Click on the Exit Design Mode icon and close the toolbar. HTH Jason Atlanta, GA ...

Entering a company name on the tab
Hi I am trying to create a tab that when I open Excel and click File New I will be able to see / General / Spreadsheet Solutions / Ashfield / I am using Excel 2000 - I know I did this before on 97 but can't remember how. I find it useful for customers who would like to store their own templates in their own area. Thanks a million for any help in advance Ann Ann What do you consider to be the "Tab"? A worksheet Tab name or the Caption you see in the Title Bar? For the former, just create a workbook with that sheet name and save as a Template. For the latter, you...

calculating quantity on hand based on date-time stamp
Hello: I am always unclear on the proper syntax for pulling date-related data. I need to pull a field in my query, below, based on the field called "IV00118.CHANGEDATE_I". This is the date in which an end user changed the cost of an item. To give you some background, the IV00102 table in my query below is the Item Quantity Master table, while the IV00118 table is the Item Cost Change History table. The field that I want to pull based on that date field is the IV00102.QTYONHND field. This field is the inventory quantity on hand field. How would I format the syntax bel...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

SUMIFS with Dates
I have a conditional SUMIFS Detail Sheet Type Date Qty Credit 2/1/10 1 Sales 2/2/10 2 Credit 2/15/10 3 Summary Sheet Week # Start Date End Date Credits Sales 1 2/1/10 2/5/10 1 2 2 2/8/10 2/15/10 3 0 So, basicallay I am summing the Credit column if the Detail is Type Credit and the Date is >= the Start Date on the line and the End Date <= the Date on the line. Same for Sales. However, as simple as it seems, it just seems not lo l...

Dynamics CRM 4.0
Hi, I've modified the format in the system settings screen to be "English (United Kingdom)", however all dates are still appearing in US format. Even when you expand the calendar control it displays incorrectly. Numeric values are correct - showing the pound sign and correct decimal point. I've restarted IIS, made sure all SQL logins are British English and the default regional settings of the server are set to British English. Even rebooted the server but this still hasn't corrected the problem. Can anybody tell me how to resolve this please Thanks I was looking ...

email sent/received dates
Hi, I'm using MSN Premium included with Verizon DSL and decided to take advantage of the 2g email storage and outlook connector for msn. Got it set up and working. I created a new folder to archive some email before I copy it to the archive pst on my pc so I can search it remotely. No problems. Today, I noticed all my dates changed on sent and received email that I moved. Worse, the header in the email (that outlook displays) shows this new date and doesn't reflect the original email date. I was moving mail from our company exchange server that was ready to be deleted to this fo...

range color from date and database
newbie done some homework on excel i would like to have a range change color based on date and time and a name from a database--or any suggestion for example if it is thursday and the time is betweeen 1 and 3 pm i want the range to be say light red if out of tinme range i want it to be light green also the cell above the range as long as time constraints are met to have a name from a datasource any suggestions or ideas apprciated i need to have data from either a database or from say another sheet where user can enter a form the data from the datasource would fill range values a...