date entered wrong or not as a date

  • Thread starter Thread starter Margo Guda
  • Start date Start date
M

Margo Guda

Hi,
In Excel 2000, I am trying to enter a series of dates, say from july 1st
2005 to february 28, 2006.
Excel does not recognize this as a date, in however form I enter it,
even if I format the cell to force it to recognize a date.
I have checked my regional settings, and updated them to use four-digit
years as a default, but this does not help. Can anyone help?
I need the worksheet to recognize a date and to be able to do
calculations with it. Now, when I enter the date in a cell and do
DAY(cell), I get a VALUE error. I suppose that means excel does not
recognize the value in the cell as a date.

Thanks for any help.
Margo Guda.
 
Any way that it worked in the past. For instance I enter January 1,
2005. Or I put 1/1/2005. Or Jan-1-2005. All of those were fine before,
but now they are just entered as strings. In the formula bar I just see
what I typed. I know something is wrong because trying to do
calculations with them returns an error.
I loaded the worksheet in Quattropro (on someone else's computer) and
there was able to enter the dates I need; when I load this back into
excel, the dates are correctly recognized. But somehow now I cannot get
a format dialog anymore. Or edit styles. Something seems seriously wrong.

Margo Guda.
 
Hi Margo,

Probably the cells were formatted as text before you entered the dates. Maybe you formatted the cells as dates after you entered
the dates. That doesn't help; if they were text they remain text.
Format the cells as General (or dates) and then enter the dates.
Does that help?
 
As a way out of scope check your short date format in your regional settings
for Date and Time. No one would spell out months for data entry (would they).

ISO short date format: yyyy-mm-dd
US short date format: mm/dd/yyyy
UK short date format: dd/mm/yyyy
at least hopefully 4 digit years are used, help avoid some of the confusions..
 
Hi Niek,
I had tried formatting the cells as dates and then entering the dates;
that did not help. I also looked at my regional settings and tried every
form of entering a date. None of the suggestions help, and now of course
I can't even format the cells because somehow format is unavailable
(nothing happens when I select it on any menu). This happened after I
found the workaround with Quattropro. There are some other cells where I
want to use a different format, and I can't even get to a format dialog
window.

Margo Guda.
 
Hi Margo,
Finally, I think we've got it.

Check your tools, options, transition (tab), uncheck transition options

Was that the problem it would be doing math on your cell
2006-12-15 would be the equivalent of -2006-12-15
I don't know how you actually enter a date with transition options on,
but you want them off so you can work in Excel as Excel. The
Quattropro gave it away must be like Lotus 1-2-3 which is what the
transisition options refer to...
 
Hi David,
Thanks but no. I never had the Transition on, even. It's something in
the worksheet I think, because in a new worksheet the problem goes away.
And now here at my office machine, in that worksheet I can't format
cells anymore either. But in a new worksheet, no problem. I made this
worksheet by opening a csv text file. I think this is at the root of the
problem. How do I resolve it?

Thanks for your input.
Margo Guda.
 
Back
Top