Company financial info dates not being read by Excel formulas.

[Eamonn100]

When I copy/paste a company's financial info from site like Yahoo and Msn The date headers of the quarters and years can't be read correctly by excel. Formulas won't read them. Does anyone know a way to fix this.

Thanks.

 

[Gerald Higgins]

It would help if you told us what the pasted dates look like.

Are they text strings ?

 

[Eamonn100]

It would help if you told us what the pasted dates look like.

Are they text strings ?

Hope this helps. I don't know what text strings are. The dates stay on the left of the cell all the other numbers stay right. It's just the dates that can't be read by formulas or formatted.

Revenue	6/30/2016	6/30/2015	6/30/2014
Total Revenue	268,785.02	263,354.29	233,402.09
Cost of Revenue	175,755.69	159,398.39	142,203.32
Gross Profit	93,029.33	103,955.90	91,198.77

<tbody>
</tbody>

 

[Gerald Higgins]

If you have a date in a cell, say cell A1, what is the result of this formula ?

=istext(a1)

If the result is TRUE, then it's a text string, which Excel won't automatically read as a date, but there are ways round it.

If the result is FALSE, then it's a number, which Excel probably can read as a date.

For the examples given, can I just check . . .
Is it
6/30/2016
or perhaps
06/30/2016
or perhaps
_6/30/2016 (where the first 6 is preceded by a space character).

What I'm getting at, is, is the length of these text strings ALWAYS 10 characters (2 for day, 2 for month, 4 for year, and 2 / )
OR, does the length vary depending on the number of digits in the day and month ?

Whatever the answer is, we can work round it, but it's easier if we know this in advance.

 

[Eamonn100]

It came back TRUE so it's a string text.

From your examples I'd say 6/30/2016 as there's no 0 or _.

Below is a row of other string text dates. The 4th (12/31/2015) is longer than the others, so I would say it changes.

9/30/2016

6/30/2016

3/31/2016

12/31/2015

<tbody>
</tbody>

Hope I've answered correctly.

 

[Gerald Higgins]

OK, so we now know we have a text string of variable length.
That's useful information.
It would be slightly easier if they were fixed length, but we should still be able to get it to work.

One more question - if the date is 1st Sep 2016, what does it look like
9/1/2016
9/01/2016
Or something else ?

 

[Eamonn100]

Oh......... almost all companies release their figures at the end of the month. It would be hard to find an example of one that releases at the start of the month, (i.e. a single digit). But going from the fact that the single digit months are only one figure, I would go with just one figure for the days as well.

9/1/2016

When I eventually format off these dates I will just show month/year.

 

[Gerald Higgins]

OK so try this to convert your input dates to Excel date values.

=DATE(RIGHT(A1,4)+0,LEFT(A1,FIND("/",A1,1)-1)+0,MID(A1,FIND("/",A1,1)+1,FIND("/",A1,4)-FIND("/",A1,1)-1)+0)

If you want MM/DD/YYYY, you can format the results to show it in that format.

 

[Eamonn100]

Cool, that worked a treat, thanks for that.

 

[Tetra201]

You could also try =VALUE(A1)