Friday, September 30, 2011

When dates are not Excel friendly

I recently had an issue.

I have a report which exports into Excel. The date format from the report is US, and I work in the UK.
Excel tries to recognise the dates as UK, so for dates that fit (eg. month is 1-12) Excel gives me an incorrect date.  For dates that don't fit (eg. month is 13-31) then Excel doesnt recognise this as a date and this is formatted as plain text.

An example of this is below:
7/29/2011 7:23:26 AM
08/02/2011 05:45
These two dates should be 29/07/2011 and 02/08/2011 respectively. However the top one is formatted as text, whereas the bottom one is formatted as date (incorrect format in terms of UK to US!)
To rectify this issue I have the formula shown below (which assumes the cell A1 contains the top of the list of exported dates)
=IF(ISNUMBER(A1),DATE(YEAR(A1), DAY(A1), MONTH(A1)),DATE(MID(A1, FIND("/", A1, 4)+1, 4), LEFT(A1, FIND("/", A1)-1), VALUE(MID(A1, FIND("/", A1)+1, 2))))
This works exceptionally well.

A user came to me today with the same issue and following giving her this solution, I thought to blog this as being worth sharing.

No comments:

Post a Comment