Imported dates that won't chart

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!!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

shinezalot

New Member
Joined
Jan 26, 2005
Messages
5
I just tried to replace CONCATENATE(TEXT(R2,"MM/DD/YYYY")," ",S2) in Q2 with DATE(YEAR(R2),MONTH(R2),DAY(R2))&" "&S2. This gives me 38095 19:19:05. My results still won't chart and custom formatting doesn't change the cells output at all.
 

shinezalot

New Member
Joined
Jan 26, 2005
Messages
5
I am thinking that I may have to parse the information during the import process. I’ll be checking post here to see if I can find some code that will help with this.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top