date format not working correctly

[Dennis Roman]

hello,
when i go to format a cell to have the date shown as
03/14/01 (2 digits each for month, day & year), i enter
todays date as "42804", expecting it to show up
as "04/28/04", but it comes up as "03/10/17"
other dates entered all also come up as a different date
what am i doing wrong?
also, i have excel set to the 1900 format. i thought maybe
it was in 1904, but it is set correctly....is there any
other way to fix this problem?
thank you,
dennis roman

 

[Frank Kabel]

Hi
you have to enter dates with delimiters. otherwise 42804 as interpreted
as the serial number for a date.
For a quick entry you may have a look at
http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Ron Rosenfeld]

hello,
when i go to format a cell to have the date shown as
03/14/01 (2 digits each for month, day & year), i enter
todays date as "42804", expecting it to show up
as "04/28/04", but it comes up as "03/10/17"
other dates entered all also come up as a different date
what am i doing wrong?
also, i have excel set to the 1900 format. i thought maybe
it was in 1904, but it is set correctly....is there any
other way to fix this problem?
thank you,
dennis roman

Date formatting only affects how cell contents are DISPLAYED. It has NO effect
on how dates are entered.

Dates must be entered in some format that XL can recognize. This means using
delimiters or certain textual representations.

If you want to enter numbers without delimiters, you need to convert that
number into an XL date before XL will understand it as such.

In your example, since March 10, 2017 is 42,804 days after Jan 1, 1900 and that
is why you get that value.

You can use either a VBA solution, or a formula to convert your entries.

One method of converting your entry is with the formula:

=DATEVALUE(TEXT(A1,"00\/00\/00"))

but there are many other methods.


--ron

 

[Guest]

thank you!

-----Original Message-----
Hi
you have to enter dates with delimiters. otherwise 42804 as interpreted
as the serial number for a date.
For a quick entry you may have a look at
http://www.cpearson.com/excel/DateTimeEntry.htm

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Guest]

thank you!

-----Original Message-----


Date formatting only affects how cell contents are DISPLAYED. It has NO effect
on how dates are entered.

Dates must be entered in some format that XL can recognize. This means using
delimiters or certain textual representations.

If you want to enter numbers without delimiters, you need to convert that
number into an XL date before XL will understand it as such.

In your example, since March 10, 2017 is 42,804 days after Jan 1, 1900 and that
is why you get that value.

You can use either a VBA solution, or a formula to convert your entries.

One method of converting your entry is with the formula:

=DATEVALUE(TEXT(A1,"00\/00\/00"))

but there are many other methods.


--ron
.