Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format text to date results in ##############
In the "old days" you used to be able to type a number into Excel as 11301967
and then use the Format Cells command to have it formatted as a date, such as 11/30/67. In version 2007, I noticed that doing so results in two things, first the Format Cells (both dates and times are affected) display a sample string of #### instead of the display of what the date will be formatted as. Selecting the date format results in width of the cell filling with #, and displays a tooltip message of "dates and times that are negative or too large display as #######" I followed the instructions for one post which advised to use Date/Text to Columns, which worked fine and resolved the issues (and for both 2 and 4-character years), but can someone explain as to why this occurs? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
format text to date results in ##############
In Excel, dates are really just integer numbers that are formatted to look
like a date. These numbers are simply the count of days starting from a base date. The default base date is 1/1/1900. The numeric value of 1/1/1900 is 1. Each successive day the numeric value increases by 1. So, 1/2/1900 has a numeric value of 2. 1/3/1900 has a numeric value of 3. Etc., etc., etc. Today's date is 4/25/2010. 4/25/2010 has the numeric value of 40293. These integer numbers are also known as the date serial numbers. 11/30/1967 = date serial number 24806. To see this in "action" enter some random date in a cell. If you enter the date in a true Excel date format Excel will automatically recognize the entry as a date and will automatically apply a date format to the entry. Now, change the format of that cell to General. You'll see the cell now contains an integer, the date serial number. The reason you get all those "hash marks" when you try to format the number 11301967 as a date is because Excel sees that as a date serial number *but* Excel only recognizes dates up to 12/31/9999. 12/31/9999 = date serial number 2958465. So, the number 11301967 exceeds the largest date serial number that Excel will evaluate as a date. -- Biff Microsoft Excel MVP "EmpressTarr" wrote in message ... In the "old days" you used to be able to type a number into Excel as 11301967 and then use the Format Cells command to have it formatted as a date, such as 11/30/67. In version 2007, I noticed that doing so results in two things, first the Format Cells (both dates and times are affected) display a sample string of #### instead of the display of what the date will be formatted as. Selecting the date format results in width of the cell filling with #, and displays a tooltip message of "dates and times that are negative or too large display as #######" I followed the instructions for one post which advised to use Date/Text to Columns, which worked fine and resolved the issues (and for both 2 and 4-character years), but can someone explain as to why this occurs? Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
format text to date results in ##############
You mis-remember the old days.
You could never do that using formatting alone. Excel 2003 will give you a cell full of ############### same as 2007 Gord Dibben MS Excel MVP On Sun, 25 Apr 2010 07:30:01 -0700, EmpressTarr wrote: In the "old days" you used to be able to type a number into Excel as 11301967 and then use the Format Cells command to have it formatted as a date, such as 11/30/67. In version 2007, I noticed that doing so results in two things, first the Format Cells (both dates and times are affected) display a sample string of #### instead of the display of what the date will be formatted as. Selecting the date format results in width of the cell filling with #, and displays a tooltip message of "dates and times that are negative or too large display as #######" I followed the instructions for one post which advised to use Date/Text to Columns, which worked fine and resolved the issues (and for both 2 and 4-character years), but can someone explain as to why this occurs? Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
format text to date results in ##############
Thank you so much for your prompt, professional, and most of all clear and
helpful information. "T. Valko" wrote: In Excel, dates are really just integer numbers that are formatted to look like a date. These numbers are simply the count of days starting from a base date. The default base date is 1/1/1900. The numeric value of 1/1/1900 is 1. Each successive day the numeric value increases by 1. So, 1/2/1900 has a numeric value of 2. 1/3/1900 has a numeric value of 3. Etc., etc., etc. Today's date is 4/25/2010. 4/25/2010 has the numeric value of 40293. These integer numbers are also known as the date serial numbers. 11/30/1967 = date serial number 24806. To see this in "action" enter some random date in a cell. If you enter the date in a true Excel date format Excel will automatically recognize the entry as a date and will automatically apply a date format to the entry. Now, change the format of that cell to General. You'll see the cell now contains an integer, the date serial number. The reason you get all those "hash marks" when you try to format the number 11301967 as a date is because Excel sees that as a date serial number *but* Excel only recognizes dates up to 12/31/9999. 12/31/9999 = date serial number 2958465. So, the number 11301967 exceeds the largest date serial number that Excel will evaluate as a date. -- Biff Microsoft Excel MVP "EmpressTarr" wrote in message ... In the "old days" you used to be able to type a number into Excel as 11301967 and then use the Format Cells command to have it formatted as a date, such as 11/30/67. In version 2007, I noticed that doing so results in two things, first the Format Cells (both dates and times are affected) display a sample string of #### instead of the display of what the date will be formatted as. Selecting the date format results in width of the cell filling with #, and displays a tooltip message of "dates and times that are negative or too large display as #######" I followed the instructions for one post which advised to use Date/Text to Columns, which worked fine and resolved the issues (and for both 2 and 4-character years), but can someone explain as to why this occurs? Thank you. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
format text to date results in ##############
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "EmpressTarr" wrote in message ... Thank you so much for your prompt, professional, and most of all clear and helpful information. "T. Valko" wrote: In Excel, dates are really just integer numbers that are formatted to look like a date. These numbers are simply the count of days starting from a base date. The default base date is 1/1/1900. The numeric value of 1/1/1900 is 1. Each successive day the numeric value increases by 1. So, 1/2/1900 has a numeric value of 2. 1/3/1900 has a numeric value of 3. Etc., etc., etc. Today's date is 4/25/2010. 4/25/2010 has the numeric value of 40293. These integer numbers are also known as the date serial numbers. 11/30/1967 = date serial number 24806. To see this in "action" enter some random date in a cell. If you enter the date in a true Excel date format Excel will automatically recognize the entry as a date and will automatically apply a date format to the entry. Now, change the format of that cell to General. You'll see the cell now contains an integer, the date serial number. The reason you get all those "hash marks" when you try to format the number 11301967 as a date is because Excel sees that as a date serial number *but* Excel only recognizes dates up to 12/31/9999. 12/31/9999 = date serial number 2958465. So, the number 11301967 exceeds the largest date serial number that Excel will evaluate as a date. -- Biff Microsoft Excel MVP "EmpressTarr" wrote in message ... In the "old days" you used to be able to type a number into Excel as 11301967 and then use the Format Cells command to have it formatted as a date, such as 11/30/67. In version 2007, I noticed that doing so results in two things, first the Format Cells (both dates and times are affected) display a sample string of #### instead of the display of what the date will be formatted as. Selecting the date format results in width of the cell filling with #, and displays a tooltip message of "dates and times that are negative or too large display as #######" I followed the instructions for one post which advised to use Date/Text to Columns, which worked fine and resolved the issues (and for both 2 and 4-character years), but can someone explain as to why this occurs? Thank you. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
Arithmatic functions with date format...angry results | Excel Worksheet Functions | |||
change TEXT format to number, keep 00 neg results.. | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Why does formula show rather than results (not in text format) | Excel Worksheet Functions |