Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Format
I have copied data from a Word table into and Excel worksheet. One column
contains dates from the mid-1800's to the 2000's. In Word they mostly look like dd-mmm-yyyy. However, not all cells are populated completely and may only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy. I need to keep have an excel format of dd-mmm-yyyy. However, when I put this format on the column I get the following: Original WORD Data: 7-Aug-1915 Excel Data (pre formatting): 7-Aug-15 Excel Data (post formatting): 7-Aug-1915 Original WORD Data: 1909 Excel Data (pre formatting): 1909 Excel Data (post formatting): 23-Mar-1905 How can I force Excel to use 4 digits in the year without loosing my data that does not include the complete date, but only a partial date? I thank you for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Format
If you click on the cell that shows 1909, what shows in the formula bar?
"KathyNeedsHelp" wrote: I have copied data from a Word table into and Excel worksheet. One column contains dates from the mid-1800's to the 2000's. In Word they mostly look like dd-mmm-yyyy. However, not all cells are populated completely and may only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy. I need to keep have an excel format of dd-mmm-yyyy. However, when I put this format on the column I get the following: Original WORD Data: 7-Aug-1915 Excel Data (pre formatting): 7-Aug-15 Excel Data (post formatting): 7-Aug-1915 Original WORD Data: 1909 Excel Data (pre formatting): 1909 Excel Data (post formatting): 23-Mar-1905 How can I force Excel to use 4 digits in the year without loosing my data that does not include the complete date, but only a partial date? I thank you for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Format
To excel, all dates are just numbers--a count of days since a starting point.
Most wintel users use Jan 1, 1900. So when you format 1909 as a date, you're really counting 1909 days since Jan 1, 1900. And that turns out to be about 5 years later (Mar 23, 1905). If you want to convert that number to a real date, you could use a helper column and a formula like: =date(a1,1,1) (January 1, of the year in A1). The other problem you have is that your dates before that starting date (pre-1900) won't be treated as dates by excel. But John Walkenbach has some utilities that may help you: http://spreadsheetpage.com/index.php...ate_functions/ KathyNeedsHelp wrote: I have copied data from a Word table into and Excel worksheet. One column contains dates from the mid-1800's to the 2000's. In Word they mostly look like dd-mmm-yyyy. However, not all cells are populated completely and may only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy. I need to keep have an excel format of dd-mmm-yyyy. However, when I put this format on the column I get the following: Original WORD Data: 7-Aug-1915 Excel Data (pre formatting): 7-Aug-15 Excel Data (post formatting): 7-Aug-1915 Original WORD Data: 1909 Excel Data (pre formatting): 1909 Excel Data (post formatting): 23-Mar-1905 How can I force Excel to use 4 digits in the year without loosing my data that does not include the complete date, but only a partial date? I thank you for your help. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Format
The erroneous date appears in the formula bar. So, in the 1909 example, I
would see 3/23/1905 in the formula bar. I could live with this information going into Excel as text rather than dates, but I don't seem "Sean Timmons" wrote: If you click on the cell that shows 1909, what shows in the formula bar? "KathyNeedsHelp" wrote: I have copied data from a Word table into and Excel worksheet. One column contains dates from the mid-1800's to the 2000's. In Word they mostly look like dd-mmm-yyyy. However, not all cells are populated completely and may only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy. I need to keep have an excel format of dd-mmm-yyyy. However, when I put this format on the column I get the following: Original WORD Data: 7-Aug-1915 Excel Data (pre formatting): 7-Aug-15 Excel Data (post formatting): 7-Aug-1915 Original WORD Data: 1909 Excel Data (pre formatting): 1909 Excel Data (post formatting): 23-Mar-1905 How can I force Excel to use 4 digits in the year without loosing my data that does not include the complete date, but only a partial date? I thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Convert date from text format to date format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |