Betfair really are a pain.
I've been looking at some files (Directory listings) for Nov 2017 and attach a small sample of dates (don't forget this is Novembers listings).
Looking at this it appears like you've got April,September and December included and excel treats it exactly as that.A right cock up if you do any monthly analysis
Betfair Dates-Caution!
I think the Betfair dates are correct but Excel is treating the dates as American format and therefore converting them incorrectly.
For example, 9th November equates to 9/11/18. If you interpret that as an American date (m/d/y format), then you would get 11th September.
Similarly, 4th November equates to 4/11/18. If you interpret that as an American date (m/d/y format), then you would get 11th April.
For example, 9th November equates to 9/11/18. If you interpret that as an American date (m/d/y format), then you would get 11th September.
Similarly, 4th November equates to 4/11/18. If you interpret that as an American date (m/d/y format), then you would get 11th April.
Yes, you can format them so they all look like Nov dates, E2 is formatted as "dd/mm/yyyy hh:mm" and you can format it as "mm/dd/yyyy hh:mm"
but it only looks like 09/11/2017 16:05:00. Click on the cell and look in the formula bar, excel still thinks it's a September date. Real pain.
but it only looks like 09/11/2017 16:05:00. Click on the cell and look in the formula bar, excel still thinks it's a September date. Real pain.
A slight miscommunication on my part.
I'm not talking about formatting the output, I'm talking about the format used by Excel when IMPORTING the data.
If Excel is importing the dates as if they are US format, then don't you end up with the mix of dates that you put up in your original posting?
As the "error" occurs on IMPORT, this is why the date is seen internally as September or April in Excel regardless of the OUTPUT format.
It's difficult to comment fully without seeing the data that you are trying to import in its native format.
I'm not talking about formatting the output, I'm talking about the format used by Excel when IMPORTING the data.
If Excel is importing the dates as if they are US format, then don't you end up with the mix of dates that you put up in your original posting?
As the "error" occurs on IMPORT, this is why the date is seen internally as September or April in Excel regardless of the OUTPUT format.
It's difficult to comment fully without seeing the data that you are trying to import in its native format.
Your right. Just checked the source data :http://www.betfairpromo.com/betfairsp/prices/
Seems to be a bug somewhere along the line when copy/pasting.I'll go down the import route.
Thanks for your help.
Seems to be a bug somewhere along the line when copy/pasting.I'll go down the import route.
Thanks for your help.