incorrect date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

As of yesterday, 3/16/04, when you format a cell in Excel for the date, the date is not correct...I enter 031704 and the date appears as October 19, 1986. What ever date you type in it appears incorrect. We have tried on 97, 98, 2000, XP and even on a mac version, they all come up with the incorrect date. We have tried on our home computers and it's incorrect there also. I work in accounting and I have a spreadsheet that I have been using for years in Excel and I have always entered the date this way in my date formatted cell, 031704 on my spreadsheet to come up with the date of March 17, 2004. Any help would be appreciated!
 
Change the format of the cell to general. You'll see that the 31,705 i
the number behind the excel date of 10/19/86. Change the number t
31,706 and then change the format back to date. The date will b
10/21/1986. Enter it with the slashes and you won't have a problem
 
This is because you are not inputting as a date, but as a number. Excel then
treats this simply as a number, and when you format as a date, it calculates
that number (31,407) no of days since 1st Jan 1900, which is 19th Oct 1986.

Input the date as 03/17/04 and all will be well.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

ad place said:
As of yesterday, 3/16/04, when you format a cell in Excel for the date,
Click to expand...
the date is not correct...I enter 031704 and the date appears as October 19,
1986. What ever date you type in it appears incorrect. We have tried on 97,
98, 2000, XP and even on a mac version, they all come up with the incorrect
date. We have tried on our home computers and it's incorrect there also. I
work in accounting and I have a spreadsheet that I have been using for years
in Excel and I have always entered the date this way in my date formatted
cell, 031704 on my spreadsheet to come up with the date of March 17, 2004.
Any help would be appreciated!
 
How should Excel know that 31704 is a date and not your income, or the
amount of red ants passing a particular flower at sunset ? She doesn't.
Enter dates as you would in writing, with some separators here and there.

The strange result is because Microsoft dates is "days since new year to
1900". And Oct 19th 1986 is 31704 days after just that. (A Mac should in
theory produce another value though, some day Oct 2000 using default
settings...)

HTH. Best wishes Harald

ad place said:
As of yesterday, 3/16/04, when you format a cell in Excel for the date,
Click to expand...
the date is not correct...I enter 031704 and the date appears as October 19,
1986. What ever date you type in it appears incorrect. We have tried on 97,
98, 2000, XP and even on a mac version, they all come up with the incorrect
date. We have tried on our home computers and it's incorrect there also. I
work in accounting and I have a spreadsheet that I have been using for years
in Excel and I have always entered the date this way in my date formatted
cell, 031704 on my spreadsheet to come up with the date of March 17, 2004.
Any help would be appreciated!
 
Hi
in addition to the explanations you received see the following site for
a workaround (using event procedures)
http://www.cpearson.com/excel/DateTimeEntry.htm

--
Regards
Frank Kabel
Frankfurt, Germany

ad place said:
As of yesterday, 3/16/04, when you format a cell in Excel for the
Click to expand...
date, the date is not correct...I enter 031704 and the date appears as
October 19, 1986. What ever date you type in it appears incorrect. We
have tried on 97, 98, 2000, XP and even on a mac version, they all come
up with the incorrect date. We have tried on our home computers and
it's incorrect there also. I work in accounting and I have a
spreadsheet that I have been using for years in Excel and I have always
entered the date this way in my date formatted cell, 031704 on my
spreadsheet to come up with the date of March 17, 2004. Any help would
be appreciated!
 
I have formatted this cell to know that it is a date and 031704 would format to March 17, 2004. This has worked for many years until 3:00 yesterday.
 
ad place said:
I have formatted this cell to know that it is a date and 031704 would
Click to expand...
format to March 17, 2004. This has worked for many years until 3:00
yesterday.

I doubt that, unless you talk about a particular file or set of files. It's
absolutely possible to make a macro or some functions that parses those
numerical inputs and convert them to dates. What many users do is copy the
cells into new worksheets and there they are but not the corresponding
macros. Or, if your macro security settings changed from Run them to Disable
them, then they won't work anymore in their own files either. But trust me,
Excel never ever worked that way by default.

HTH. Best wishes Harald
 
Hi
I'm quite sure that this could never have worked before unleass you had
a kind of macro installed for this. Excel does not have this feature!

Maybe you had an add-in installed or you had a specific workbook (with
event macros).

--
Regards
Frank Kabel
Frankfurt, Germany

ad place said:
Until yesterday, I have never had to imput with slashes, this is a
Click to expand...
form my department has used for years and when we imput 031704 the date
would also show as March 17, 2004. This is what has me so confused! My
department uses this same Excel worksheet and everyone as of yesterday
started having this same problem. I completed several worksheets
before 3:00 yesterday enetering the date of 031604 and coming up with
March 16, 2004 and after 3:00 the date would come up wrong!
 
I created the form in question 3 years ago and formatted cells with the simple right click of the mouse. I have used this form daily for the last 3 years and have always entered the date 031704 with no separators of any kind as I do in my accounting software MAS90 and the date has always come up correct...I'm really not crazy!!
 
ad place said:
I have used this form daily for the last 3 years and have always entered
Click to expand...
the date 031704 with no separators of any kind as I do in my accounting
software MAS90 and the date has always come up correct...I'm really not
crazy!!

Of course not. We're all your friends here. Especially the big guys in white
clothes :-)
No, sorry, seriously. 031704 is NOT a date, it's a number. You can preformat
a cell as Text (NOT date or number) and it will display exactly whatever you
enter. If you think and interprete that as a date then great, and if that's
all you need then format cell as Text.

But Excel will not ever see it as a date. She thinks of it as a number (if
cell is unformatted), or a text (if formatted as text), or as days since the
new year to 1900 (if cell is formatted as date).

HTH. Best wishes Harald
 
Hi Ad Place!

Take it from us, unless there is an event handling subroutine that
steps between your data entry you cannot enter the dates as you say.

I would suggest that you had a template where the date entry field was
covered by the routine. Sometime, Yesterday, someone managed to change
the template and that is what is causing the difference.

I'm ruling out supernatural intervening events and fairies.

By all means send me one of the workbooks created before this change
and I'm sure I'll find the subroutine there.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
ad place said:
I created the form in question 3 years ago and formatted cells with
Click to expand...
the simple right click of the mouse. I have used this form daily for
the last 3 years and have always entered the date 031704 with no
separators of any kind as I do in my accounting software MAS90 and the
date has always come up correct...I'm really not crazy!!
 
Take it from us, unless there is an event handling subroutine that
steps between your data entry you cannot enter the dates as you say.

I would suggest that you had a template where the date entry field was
covered by the routine. Sometime, Yesterday, someone managed to change
the template and that is what is causing the difference.

I'm ruling out supernatural intervening events and fairies.

By all means send me one of the workbooks created before this change
and I'm sure I'll find the subroutine there.
Click to expand...

Norman
and please post back your result (just curious now) :-)

Frank
 
Hi Frank!

As you know, it just has to be a subroutine.

Formatting a number cannot serve to translate that number into a date other
than the date represented by the date serial number that is input.

But we'll see if he takes up the offer of sending a "pre-Yesterday"
workbook.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top