How do I get Excel date to change from 1/1/1900?

[Debra Dalgleish]

What do you see in the formula bar when you select that cell?

What shows in the cell if you hold the Ctrl key, and type a semi-colon,
to enter the current date?

Macdad wrote:
> I am entering the date (8/25/2005) in a cell that is date-formatted.
> However, once entered the cell always displays 1/1/1900 (Excel's embedded
> calendar start date), i.e. it doesn't display the date I entered. How do I
> get it to display teh date I entered?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Macdad]

Thank you very much for responding Debra.

What I see in the formula bar when I select that cell: =8/25/5

When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
the number 0.0001596 is displayed in the cell and if I pick the cell again, I
see
=8/25/2005 in the formula bar.

"Debra Dalgleish" wrote:

> What do you see in the formula bar when you select that cell?
>
> What shows in the cell if you hold the Ctrl key, and type a semi-colon,
> to enter the current date?
>
> Macdad wrote:
> > I am entering the date (8/25/2005) in a cell that is date-formatted.
> > However, once entered the cell always displays 1/1/1900 (Excel's embedded
> > calendar start date), i.e. it doesn't display the date I entered. How do I
> > get it to display teh date I entered?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Macdad]

This is a correction to my last post (the responses I gave are when the cell
was incorrectly formatted as "General). When the cell is properly formatted
as a "Date":

What I see in the formula bar when I select the cell: =8/25/5

What I see in the cell if I hold the Ctrl key, and type a semi-colon:
8/25/05 in the cell and in the formula bar. If I then hit enter, the cell
displays 1/0/00. If I pick the cell again, I see 1/0/00 in the cell and
=8/25/2005 in the formula bar.



"Debra Dalgleish" wrote:

> What do you see in the formula bar when you select that cell?
>
> What shows in the cell if you hold the Ctrl key, and type a semi-colon,
> to enter the current date?
>
> Macdad wrote:
> > I am entering the date (8/25/2005) in a cell that is date-formatted.
> > However, once entered the cell always displays 1/1/1900 (Excel's embedded
> > calendar start date), i.e. it doesn't display the date I entered. How do I
> > get it to display teh date I entered?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

From the Tools menu, choose Options
On the Transition tab, remove the check mark from 'Transition Formula Entry'
Click OK

Macdad wrote:
> Thank you very much for responding Debra.
>
> What I see in the formula bar when I select that cell: =8/25/5
>
> When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
> the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
> the number 0.0001596 is displayed in the cell and if I pick the cell again, I
> see
> =8/25/2005 in the formula bar.
>
> "Debra Dalgleish" wrote:
>
>
>>What do you see in the formula bar when you select that cell?
>>
>>What shows in the cell if you hold the Ctrl key, and type a semi-colon,
>>to enter the current date?
>>
>>Macdad wrote:
>>
>>>I am entering the date (8/25/2005) in a cell that is date-formatted.
>>>However, once entered the cell always displays 1/1/1900 (Excel's embedded
>>>calendar start date), i.e. it doesn't display the date I entered. How do I
>>>get it to display teh date I entered?
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Macdad]

That worked. Thanks Debra.

"Debra Dalgleish" wrote:

> From the Tools menu, choose Options
> On the Transition tab, remove the check mark from 'Transition Formula Entry'
> Click OK
>
> Macdad wrote:
> > Thank you very much for responding Debra.
> >
> > What I see in the formula bar when I select that cell: =8/25/5
> >
> > When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
> > the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
> > the number 0.0001596 is displayed in the cell and if I pick the cell again, I
> > see
> > =8/25/2005 in the formula bar.
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>What do you see in the formula bar when you select that cell?
> >>
> >>What shows in the cell if you hold the Ctrl key, and type a semi-colon,
> >>to enter the current date?
> >>
> >>Macdad wrote:
> >>
> >>>I am entering the date (8/25/2005) in a cell that is date-formatted.
> >>>However, once entered the cell always displays 1/1/1900 (Excel's embedded
> >>>calendar start date), i.e. it doesn't display the date I entered. How do I
> >>>get it to display teh date I entered?
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.

Macdad wrote:
> That worked. Thanks Debra.
>
> "Debra Dalgleish" wrote:
>
>
>> From the Tools menu, choose Options
>>On the Transition tab, remove the check mark from 'Transition Formula Entry'
>>Click OK
>>
>>Macdad wrote:
>>
>>>Thank you very much for responding Debra.
>>>
>>>What I see in the formula bar when I select that cell: =8/25/5
>>>
>>>When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
>>>the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
>>>the number 0.0001596 is displayed in the cell and if I pick the cell again, I
>>>see
>>>=8/25/2005 in the formula bar.
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>What do you see in the formula bar when you select that cell?
>>>>
>>>>What shows in the cell if you hold the Ctrl key, and type a semi-colon,
>>>>to enter the current date?
>>>>
>>>>Macdad wrote:
>>>>
>>>>
>>>>>I am entering the date (8/25/2005) in a cell that is date-formatted.
>>>>>However, once entered the cell always displays 1/1/1900 (Excel's embedded
>>>>>calendar start date), i.e. it doesn't display the date I entered. How do I
>>>>>get it to display teh date I entered?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Macdad]

I am entering the date (8/25/2005) in a cell that is date-formatted.
However, once entered the cell always displays 1/1/1900 (Excel's embedded
calendar start date), i.e. it doesn't display the date I entered. How do I
get it to display teh date I entered?

[Debra Dalgleish]

What do you see in the formula bar when you select that cell?

What shows in the cell if you hold the Ctrl key, and type a semi-colon,
to enter the current date?

Macdad wrote:
> I am entering the date (8/25/2005) in a cell that is date-formatted.
> However, once entered the cell always displays 1/1/1900 (Excel's embedded
> calendar start date), i.e. it doesn't display the date I entered. How do I
> get it to display teh date I entered?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Macdad]

Thank you very much for responding Debra.

What I see in the formula bar when I select that cell: =8/25/5

When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
the number 0.0001596 is displayed in the cell and if I pick the cell again, I
see
=8/25/2005 in the formula bar.

"Debra Dalgleish" wrote:

> What do you see in the formula bar when you select that cell?
>
> What shows in the cell if you hold the Ctrl key, and type a semi-colon,
> to enter the current date?
>
> Macdad wrote:
> > I am entering the date (8/25/2005) in a cell that is date-formatted.
> > However, once entered the cell always displays 1/1/1900 (Excel's embedded
> > calendar start date), i.e. it doesn't display the date I entered. How do I
> > get it to display teh date I entered?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Macdad]

This is a correction to my last post (the responses I gave are when the cell
was incorrectly formatted as "General). When the cell is properly formatted
as a "Date":

What I see in the formula bar when I select the cell: =8/25/5

What I see in the cell if I hold the Ctrl key, and type a semi-colon:
8/25/05 in the cell and in the formula bar. If I then hit enter, the cell
displays 1/0/00. If I pick the cell again, I see 1/0/00 in the cell and
=8/25/2005 in the formula bar.



"Debra Dalgleish" wrote:

> What do you see in the formula bar when you select that cell?
>
> What shows in the cell if you hold the Ctrl key, and type a semi-colon,
> to enter the current date?
>
> Macdad wrote:
> > I am entering the date (8/25/2005) in a cell that is date-formatted.
> > However, once entered the cell always displays 1/1/1900 (Excel's embedded
> > calendar start date), i.e. it doesn't display the date I entered. How do I
> > get it to display teh date I entered?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

From the Tools menu, choose Options
On the Transition tab, remove the check mark from 'Transition Formula Entry'
Click OK

Macdad wrote:
> Thank you very much for responding Debra.
>
> What I see in the formula bar when I select that cell: =8/25/5
>
> When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
> the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
> the number 0.0001596 is displayed in the cell and if I pick the cell again, I
> see
> =8/25/2005 in the formula bar.
>
> "Debra Dalgleish" wrote:
>
>
>>What do you see in the formula bar when you select that cell?
>>
>>What shows in the cell if you hold the Ctrl key, and type a semi-colon,
>>to enter the current date?
>>
>>Macdad wrote:
>>
>>>I am entering the date (8/25/2005) in a cell that is date-formatted.
>>>However, once entered the cell always displays 1/1/1900 (Excel's embedded
>>>calendar start date), i.e. it doesn't display the date I entered. How do I
>>>get it to display teh date I entered?
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Macdad]

That worked. Thanks Debra.

"Debra Dalgleish" wrote:

> From the Tools menu, choose Options
> On the Transition tab, remove the check mark from 'Transition Formula Entry'
> Click OK
>
> Macdad wrote:
> > Thank you very much for responding Debra.
> >
> > What I see in the formula bar when I select that cell: =8/25/5
> >
> > When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
> > the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
> > the number 0.0001596 is displayed in the cell and if I pick the cell again, I
> > see
> > =8/25/2005 in the formula bar.
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>What do you see in the formula bar when you select that cell?
> >>
> >>What shows in the cell if you hold the Ctrl key, and type a semi-colon,
> >>to enter the current date?
> >>
> >>Macdad wrote:
> >>
> >>>I am entering the date (8/25/2005) in a cell that is date-formatted.
> >>>However, once entered the cell always displays 1/1/1900 (Excel's embedded
> >>>calendar start date), i.e. it doesn't display the date I entered. How do I
> >>>get it to display teh date I entered?
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.

Macdad wrote:
> That worked. Thanks Debra.
>
> "Debra Dalgleish" wrote:
>
>
>> From the Tools menu, choose Options
>>On the Transition tab, remove the check mark from 'Transition Formula Entry'
>>Click OK
>>
>>Macdad wrote:
>>
>>>Thank you very much for responding Debra.
>>>
>>>What I see in the formula bar when I select that cell: =8/25/5
>>>
>>>When I hold the Ctrl key and type semi-colon, I see 8/25/2005 displayed in
>>>the cell, and 8/25/2005 dsiplayed in the formula bar. When I hit <enter>
>>>the number 0.0001596 is displayed in the cell and if I pick the cell again, I
>>>see
>>>=8/25/2005 in the formula bar.
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>What do you see in the formula bar when you select that cell?
>>>>
>>>>What shows in the cell if you hold the Ctrl key, and type a semi-colon,
>>>>to enter the current date?
>>>>
>>>>Macdad wrote:
>>>>
>>>>
>>>>>I am entering the date (8/25/2005) in a cell that is date-formatted.
>>>>>However, once entered the cell always displays 1/1/1900 (Excel's embedded
>>>>>calendar start date), i.e. it doesn't display the date I entered. How do I
>>>>>get it to display teh date I entered?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[rdoi]

I want to add to the thread that the fix in Excel 2013 is to go to File > Options > Advanced -- scroll all the way down to the bottom and uncheck the "Transition formula evaluation" and "transition formula entry". That will fix your problem with the cell automatically changing data to formulas.

Thank you for this post so many years ago. I couldn't find anything else that resembled my problem and this one led me to the resolution.