Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default convert number to date in excel 2003

I used to be able to type 070509 for date and then it'd automaticly converted
to 07-05-2009. But now it shows 15-01-2093 instead of 07-05-2009! Why? And
how to change it that i would be able to convert or change the format to the
exact date and year I want?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default convert number to date in excel 2003

Unless you were running some kind of macro or using a formula, then typing
070509 would never result in a date like 07-05-2009.

The reason that you're getting that date way out in the future is because of the
way excel treats dates. To excel, a date is just the number of days since a
starting date (usually Dec 31, 1899 in wintel land).

Chip Pearson has an event macro that can be used to quickly enter dates:
http://www.cpearson.com/Excel/DateTimeEntry.htm

Or you could use a formula in another cell that parses your entry and translates
it into a date.

But I'm not sure if 070509 is July 05, 2009 or what...


Wiji wrote:

I used to be able to type 070509 for date and then it'd automaticly converted
to 07-05-2009. But now it shows 15-01-2093 instead of 07-05-2009! Why? And
how to change it that i would be able to convert or change the format to the
exact date and year I want?


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default convert number to date in excel 2003

If you used to be able to do that you must have had some event code behind
the worksheet.

Excel has never natively been able to do that with any type of formatting.

You can use a helper columnwith formulas to make the necessary changes or
Ron de Bruin's QED add-in.

http://www.rondebruin.nl/qde.htm

What works most of the time is to enter the dates as your example then run
them through DataText to Columns.

Depending upon your Regional date settings you would get 05-07-2009 or
07-05-2009

When describing problems with dates it is best to provide a non-ambiguous
date where month and day are easily recognized.


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 07:28:01 -0700, Wiji
wrote:

I used to be able to type 070509 for date and then it'd automaticly converted
to 07-05-2009. But now it shows 15-01-2093 instead of 07-05-2009! Why? And
how to change it that i would be able to convert or change the format to the
exact date and year I want?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do you convert a number into a date JCM Excel Worksheet Functions 2 April 6th 07 02:26 AM
convert date to day of week in excel 2003 medicineman Excel Discussion (Misc queries) 1 March 12th 07 12:50 AM
Text, Number and Date formats excel 2003 driving experienced user E F Bat Excel Discussion (Misc queries) 1 January 6th 06 08:10 PM
How do I convert Date serial number to date rdunne Excel Worksheet Functions 1 April 12th 05 03:04 PM
How do I convert a number formated as a date to text in Excel? BrotherNov Excel Discussion (Misc queries) 5 March 2nd 05 04:51 PM


All times are GMT +1. The time now is 06:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"