shinezalot
New Member
- Joined
- Jan 26, 2005
- Messages
- 5
Hi all:
Ok….so I am trying to help someone out that uses Excel to manually import text files and manually chart data that is imported. The most significant column of data that is imported is a date-time stamp that looks like this when it arrives in Excel: 18 APR 2004 19:19:05 731451us –0700. After writing some code that automates the import process, I wrote a series of formulas that takes the date information in my column “D” and converts it to something that looks like this: 04/18/2004 19:19:05. The formulas I wrote are below:
In “S2” IF(ISERROR(TRIM(RIGHT(TRIM(LEFT(Import!$D2,22)),8))),"",TRIM(RIGHT(TRIM(LEFT(Import!$D2,22)),8)))
In “R2”
IF(ISERROR(EDATE(TRIM(RIGHT(TRIM(LEFT(Import!$D2,12)),12)),0)),"",EDATE(TRIM(RIGHT(TRIM(LEFT(Import!$D2,12)),12)),0))
In “Q2”
IF(ISERROR(CONCATENATE(TEXT(R2,"MM/DD/YYYY")," ",S2)),"",CONCATENATE(TEXT(R2,"MM/DD/YYYY")," ",S2))
The frustration I am having is in charting. It seems that Excel charts won’t use the “dates” that I have in column “Q”. I have done some testing and it appears that charts seem to dislike the values produced by my formulas. Maybe a better way to put it is that my formulas do not appear to be producing dates and times. My charts won’t show any data.
If someone could give me some ideas as to how I can take a column of text that looks like: 18 APR 2004 19:19:05 731451us –0700 and chart it, I would really appreciate it greatly.
Thanks everyone!!
Ok….so I am trying to help someone out that uses Excel to manually import text files and manually chart data that is imported. The most significant column of data that is imported is a date-time stamp that looks like this when it arrives in Excel: 18 APR 2004 19:19:05 731451us –0700. After writing some code that automates the import process, I wrote a series of formulas that takes the date information in my column “D” and converts it to something that looks like this: 04/18/2004 19:19:05. The formulas I wrote are below:
In “S2” IF(ISERROR(TRIM(RIGHT(TRIM(LEFT(Import!$D2,22)),8))),"",TRIM(RIGHT(TRIM(LEFT(Import!$D2,22)),8)))
In “R2”
IF(ISERROR(EDATE(TRIM(RIGHT(TRIM(LEFT(Import!$D2,12)),12)),0)),"",EDATE(TRIM(RIGHT(TRIM(LEFT(Import!$D2,12)),12)),0))
In “Q2”
IF(ISERROR(CONCATENATE(TEXT(R2,"MM/DD/YYYY")," ",S2)),"",CONCATENATE(TEXT(R2,"MM/DD/YYYY")," ",S2))
The frustration I am having is in charting. It seems that Excel charts won’t use the “dates” that I have in column “Q”. I have done some testing and it appears that charts seem to dislike the values produced by my formulas. Maybe a better way to put it is that my formulas do not appear to be producing dates and times. My charts won’t show any data.
If someone could give me some ideas as to how I can take a column of text that looks like: 18 APR 2004 19:19:05 731451us –0700 and chart it, I would really appreciate it greatly.
Thanks everyone!!