Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date confusion
general question here guys, I get a report that is in CSV form on a weekly
basis that inludes dates. Some of the data has the date format reversed. i.e. 02/01 instead of 01/02. I am ok with the logic of sorting this issue BUT, sometimes the data transforms into a number. i.e. 49025. why should this be? how can I convert it back to a "real" date.? TIA any ideas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date confusion
Are you doing anything other than opening the CSV in Excel? The
number i.e. 49025 is the date serial number - To convert back to a date just change the cells format: Format-Cells (Ctrl-1) Help? On Dec 21, 11:33*am, "mepetey" wrote: general question here guys, *I get a report that is in CSV form on a weekly basis that inludes dates. Some of the data has the date format reversed. i.e. 02/01 instead of 01/02. I am ok with the logic of sorting this issue BUT, sometimes the data transforms into a number. i.e. 49025. why should this be? *how can I convert it back to a "real" date.? TIA any ideas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date confusion
nope. just opening up the file. Thing is, some of the cells are ok. I
applied a TRIM and LEFT operator and in the affected cells i get a number. In the others i get part of the date. I heap big confused........... "brittonsm" wrote in message ... Are you doing anything other than opening the CSV in Excel? The number i.e. 49025 is the date serial number - To convert back to a date just change the cells format: Format-Cells (Ctrl-1) Help? On Dec 21, 11:33 am, "mepetey" wrote: general question here guys, I get a report that is in CSV form on a weekly basis that inludes dates. Some of the data has the date format reversed. i.e. 02/01 instead of 01/02. I am ok with the logic of sorting this issue BUT, sometimes the data transforms into a number. i.e. 49025. why should this be? how can I convert it back to a "real" date.? TIA any ideas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date confusion
First, I'm not sure I'd trust excel to open a .CSV file and give me the right
date for a string that was ambiguous-- 02/01 could mean February 1 of the current year. January 2 of the current year. February 2001 (no day) 2002 January (no day) I'd rename the .csv to .txt and then open it via File|Open Then I'd be able to see the text import wizard and I could specify how I want that field treated (general, date (ymd, dmy, ...), text or ignore. ===== If I enter 49025 in a cell and give it a date format, I get March 22, 2034 (with 1900 the base year). What do you see in the text file when you look at that field on that line? === If the 49025 was just some numbers you posted in the message and you are seeing some 5 digit number instead of all dates in the worksheet, then you may be looking at formulas. If you select the cell and look at the formulabar, do you see a real date? If yes, try: tools|Options|View tab|uncheck formulas (xl2003 menu system) (ctrl-` <ctrl-backquote to the left of the 1/! key on my USA keyboard is the shortcut toggle.) mepetey wrote: nope. just opening up the file. Thing is, some of the cells are ok. I applied a TRIM and LEFT operator and in the affected cells i get a number. In the others i get part of the date. I heap big confused........... "brittonsm" wrote in message ... Are you doing anything other than opening the CSV in Excel? The number i.e. 49025 is the date serial number - To convert back to a date just change the cells format: Format-Cells (Ctrl-1) Help? On Dec 21, 11:33 am, "mepetey" wrote: general question here guys, I get a report that is in CSV form on a weekly basis that inludes dates. Some of the data has the date format reversed. i.e. 02/01 instead of 01/02. I am ok with the logic of sorting this issue BUT, sometimes the data transforms into a number. i.e. 49025. why should this be? how can I convert it back to a "real" date.? TIA any ideas -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date confusion | New Users to Excel | |||
MDI Confusion | Excel Discussion (Misc queries) | |||
Sorting pivot tables - text / date confusion | Excel Discussion (Misc queries) | |||
Confusion..... | Excel Discussion (Misc queries) | |||
Date vs h:mm:ss confusion | New Users to Excel |