Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
I tried getting help with this before and did not get it resolved.
Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
The best solution is to format the cells where you have dates before you
import the data. The problem is that some cells will change and other won't bewcause some dates are illegal. If you can figure out which dates need to be revversed this code will help old_date = DateValue("3/10/08") MyMonth = Month(old_date) Myday = Day(old_date) new_date = DateSerial(Year(old_date), Myday, MyMonth) "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
Joel,
I can not format the cells before I import the data. How would I know which dates are in the wrong format. The CSV files come from a third party in the format that they are. I am processing 90 to 180 separate data files that are placed in a folder. My macro takes one file at a time and process the data. I don't know which dates need converting for the same reason. Any other suggestions? Thanks for looking. "Joel" wrote: The best solution is to format the cells where you have dates before you import the data. The problem is that some cells will change and other won't bewcause some dates are illegal. If you can figure out which dates need to be revversed this code will help old_date = DateValue("3/10/08") MyMonth = Month(old_date) Myday = Day(old_date) new_date = DateSerial(Year(old_date), Myday, MyMonth) "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
Just some ideas
1) Format worksheet to general format before importing data 2) Format certain columns in General format before importing data. I assume dates only go in certain columns "Nils Titley" wrote: Joel, I can not format the cells before I import the data. How would I know which dates are in the wrong format. The CSV files come from a third party in the format that they are. I am processing 90 to 180 separate data files that are placed in a folder. My macro takes one file at a time and process the data. I don't know which dates need converting for the same reason. Any other suggestions? Thanks for looking. "Joel" wrote: The best solution is to format the cells where you have dates before you import the data. The problem is that some cells will change and other won't bewcause some dates are illegal. If you can figure out which dates need to be revversed this code will help old_date = DateValue("3/10/08") MyMonth = Month(old_date) Myday = Day(old_date) new_date = DateSerial(Year(old_date), Myday, MyMonth) "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
Joel,
That will not work either. But thanks for the suggestion. "Joel" wrote: Just some ideas 1) Format worksheet to general format before importing data 2) Format certain columns in General format before importing data. I assume dates only go in certain columns "Nils Titley" wrote: Joel, I can not format the cells before I import the data. How would I know which dates are in the wrong format. The CSV files come from a third party in the format that they are. I am processing 90 to 180 separate data files that are placed in a folder. My macro takes one file at a time and process the data. I don't know which dates need converting for the same reason. Any other suggestions? Thanks for looking. "Joel" wrote: The best solution is to format the cells where you have dates before you import the data. The problem is that some cells will change and other won't bewcause some dates are illegal. If you can figure out which dates need to be revversed this code will help old_date = DateValue("3/10/08") MyMonth = Month(old_date) Myday = Day(old_date) new_date = DateSerial(Year(old_date), Myday, MyMonth) "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
Is there a way to check what format a cell is set to or the data is set to?
Right now the correct cells or data are set to General. If I know that the cell or data was set to date format, I could convert the data only those dates. I am not sure we are going to be able to get the data converted on a regular basis. Many people are using the data that is generated by the third party. Though I realize that is the best way. Thanks for your help. "Net_prof" wrote: Unless there is some other indicator in the record to help you determine when the use what format, you will continue to experience this problem. You need to work with the source to identify the problem and involve them in the solution. Without their help, this is why you are continuing to experience the problem. I've worked with a lot of EDI where this is also an issue. The old adage, "garbage in, garbage out" pertains here without both sides working towards fixing the problem. "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
Couldn't you just read in the CSV files and put the dates into a field that
is set to TEXT format, then you could use the SPLIT function using the "/" character as the diliminator. Then just have newDay = splitArray(0) NewMonth = splitArray(1) NewYear = splitArray(2) or something along those lines? "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Problem
I like the split function idea because the dates are not dd/mm/yyyy they
could be d/m/yyyy but the split would take care of that. But I need help with determining the format. If the format is general, I do not have to do anythiing. It is only when the dates in the CSV file are in TEXT format that I need to process them with the split function. How do I determine the format? Thanks for your help "NateBuckley" wrote: Couldn't you just read in the CSV files and put the dates into a field that is set to TEXT format, then you could use the SPLIT function using the "/" character as the diliminator. Then just have newDay = splitArray(0) NewMonth = splitArray(1) NewYear = splitArray(2) or something along those lines? "Nils Titley" wrote: I tried getting help with this before and did not get it resolved. Here is the problem. I am processing data files in a .CSV format. These files are processed in South Africa where they use the date format: dd/mm/yyyy. When I process the file if the dates are in a General Format there is no problem with the dates. But we have found that some of the files have mixed format where the dates are in General Format and some are in Date Format. The date format is the problem. Example: 12/01/2008 South Africa is actually Jan 12, 2008 In General format, when I process the data and write the date to a worksheet, the date is still 12/01/08 formatted to dd/mm/yy. If the data is in Date Format, the date printed then becomes 01/12/08 or Dec 1, 2008. Because Excel thinks the date is Dec 1, 2008. I need some one that is good with dates! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem accessing binary data from mysql data base | Excel Programming | |||
Problem with Pivot data | Excel Discussion (Misc queries) | |||
Problem analyzing my data | Excel Worksheet Functions | |||
Importing Data & Refresh Data Problem | Excel Discussion (Misc queries) | |||
data problem | Excel Programming |