formating columns – HELP!!!!!

Topic

New Reply

This is my second post of this and I’ve not had any replies-
Is it me or is it Excel??? I can certainly apprecitate some direction from anyone that would like to help, please!
My system is: Office 2000, Windows98, regional settings are 1930 to 2029, default ‘1900’:
Upon formatting a column to ‘date’ the displayed date is
NOT the date I’ve entered. This is on ANY worksheet. The populated date is c. 45,410 days ahead, (i.e., I typed 8/17/99 and upon entering, 12/15/23 populates the cell. I’m learning Excel and this is NOT helping me. This appears to be a ‘system’ problem. Do I need the Analysis Pack Add-In??? Thanks for your help,

Steve

Reply | Quote

Replies

There are a number of possibilities. First, to “Options” in the “Tool” menu. In the dialog box that results click on the “Transition” tab. At the bottom of that display see if you have either “Transition formula evaluation” or “Transition entry” selected. If you do, deselect them and try the entry again. Either of those options can cause this symptom.

If that is not it, then we need a little more information. Format the cells containing the date to display a 4 digit year (“mm/dd/yyyy”) and tell us what that year really is. It is 0023, 1923, 2023, etc.?

Reply | Quote

Thanks for responding!!!
The Sheet Options ‘transition…’ have both always been de-selected. I’ve tried the four digit date format with same results, ‘1932’. When I first encountered this problem last week it returned ‘2032’. I’m baffled. Please notice my reply to JohnBF as he is also responding to my dilemma. I appreciate your assistance.

Steve

Reply | Quote

Every single date entered is always jumped 45,410 forward?
Is this the English/European version of Windows and Excel?

When you enter =now() and format it as a number, what value is returned? (18 January 2001 should be day 36909.) If you first format a blank cell as a number and then enter =now(), does the cell return the same value? What shows if you enter 36909 as a number and then reformat it to be a date? Does the date change if you take the number cell reading 36909 and reformat it to date? Can you post exactly what date format you are using according to Format Cells, Number?

Can you check to see if you have any weird code running (intentionally or not); use Alt-F11 and examine all the modules through View Project Explorer.

In Excel Tools, Options, Transition do you have any Sheet Options checked?

What date does your PC clock show? Are you using the Gregorian Calendar (though that shouldn’t make a difference, I’m reaching)?

Reply | Quote

Thanks for responding!!
System purchased in US: W98, Pentium II, Excel 2000 in Office 2000 Pro; Settings: Gregorian Calendar; English (US); Short Date: mm,dd,yy, (I

Reply | Quote

Apparently, there is some confusion between setting the date format (which controls how the date will actually be displayed in the cell) with how the actual date ‘data’ must be entered.

011801 entered in to a date formated cell is interpreted as a numeric date serial number which does indeed translate to April 22, 1932. The ‘011801’ must be entered as 01/18/01 for Excel to recognize it as a date. Try it this way instead.

Reply | Quote

Thanks for the info.
As I’ve stated before, I can manually type the slashes, dashes, etc. but would really like to enjoy the wonders of formatting. As it is, the date format puts the slashes in for me but gives me an incorrect date. Something’s goofy with the ‘serial numbering’ of the date database, it seems to me. Thanks for your help.

Steve

Reply | Quote

Check the topic “Enter and edit data” in Excel Help. This topic also includes an expansion covering dates. Bottom line: You must enter date data with separators (which are chosen by your Regional settings in Windows). This is not like setting up a custom format that will stick things like hypens in the middle of a number so that you can type in phone numbers without the hyphens — you have to enter dates with the separators.

Reply | Quote

From your description of what you are doing, nothing is goofy, it is working exactly as it should be working. The date format is the DISPLAY format and has nothing to do with interpreting what you type into a cell, it only determines how what is in a cell is displayed. When you enter something like 11801 into a cell, Excell does not have any way of knowing that you mean this to be a date. It will put that number into the cell. If the cell is formatted as a date, then Excel will display a date that is 11,801 days from 1/1/1900 which is not 1/18/2001. When entering a date into a cell, you must type in the separator ncharacters. If you want it to work the way you described, it can be done by writing a Change event macro that will take what you type and insert the separators.

Reply | Quote

SMK, now I understand what you were expecting I’m sorry to advise you that Gene and Legare are correct; you have to type the m, d and y separators. Excel isn’t quite smart enough to do what you are expecting, among other things because it doesn’t know if 11101 is (in US mm/dd/yyyy notation) is 11/01/2001 or 01/11/2001 or 11/01/1901 or 1/11/1901, never mind that you think the format setting tells Excel what to expect.

Sorry, we all have our excel Annoyances to bear.

Reply | Quote

CMK, one other thing, you don’t need the Analysis Toolpack for what you were trying to do, but it does have a number of useful extensions; I load it for some of the date functions.

Reply | Quote

I would like to thank all of you, Legare, Gene and John for
your help and enlightenment. I’ll most assuredly have more
questions as I progress in my Office 2000 studies, so will
be in touch. Maybe when I get there, I’ll be able to someone else with these applications. Thanks again, all.

Steve

Reply | Quote