# Company financial info dates not being read by Excel formulas.

#### Eamonn100

##### Board Regular
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Gerald Higgins

##### Well-known Member
It would help if you told us what the pasted dates look like.

Are they text strings ?

#### Eamonn100

##### Board Regular
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>

Last edited:

#### Gerald Higgins

##### Well-known Member
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.

Last edited:

#### Eamonn100

##### Board Regular
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>

#### Gerald Higgins

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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.

Last edited:

#### Eamonn100

##### Board Regular
Cool, that worked a treat, thanks for that.

#### Tetra201

##### MrExcel MVP
You could also try =VALUE(A1)

Replies
1
Views
230
Replies
6
Views
336
Replies
12
Views
500
Replies
2
Views
450
Replies
0
Views
1K

1,191,050
Messages
5,984,370
Members
439,883
Latest member
onions44

### 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.

### Which adblocker are you using?

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

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