Dates are incorrect due to inconsistent formats

Hi there,

I have a large number of rows (60,000+) that holds timestamped data at 15 minute increments that I have imported from CSV files. My problem is that Excel seems to have gotten some of the dates wrong when importing it and I need to retrospectively correct it. It seems to be happening for the first 12 days of each month. For example, the date will go from 31/08/12 11:45:00 PM (correct) to 09/01/12 12:00:00 AM (incorrect). Once that gets to 09/12/12 11:45:00 PM (incorrect) the next row is 13/09/12:00:00 AM (correct). This is NOT a formatting issue - all cells are formatted as DD/MM/YYYY HH:MM:SS AM/PM and text to columns does not fix it.

I thought I could just us an IF statement to determine when the difference between two cells was more than 15 minutes but the problem is that the range is not contiguous i.e. there are periods of no measurements that have been removed so jumps in time do occur. I need a non-destructive way to fix this across many files.

Thanks.

Hey MarvinP,

Thanks for the quick response. I just tried to trim and checked for char(160) but with no luck. I suspect that when importing the DD format Excel chose to ignore the leading zero on some dates and then thought it was in MM/DD format as a result.

I have attached a small excerpt from one of the files for reference. Date error example.xlsx

Thanks

Thanks. I spend a lot of time perusing but it's my first time posting.

Thanks for the suggestion. But wouldn't that only work if all of them were using the incorrect format? The problem cells are interspersed throughout the sheets. I have so many rows that going through them manually to determine which are incorrect would be a massive undertaking.

I just took a closer look at the example file I attached..the problem appears to worse than first thought because the actual value is wrong there. I might have to manually rebuild it from scratch using the original CSV files to over come this problem. Sigh.

Originally Posted by AlKey

I was able to convert it to dates by using this formula

=IFERROR(--TRIM(A1),TRIM(A1))

Thanks for your help. I tried this and it does convert the cells to dates, but the resulting numbers are still incorrect for the rows where the dd/mm have been swapped for mm/dd during the import process.

Originally Posted by FDibbins

OK here is a bit of a long-winded way around what you are doing, using 2 helper columns. I used D and E.

In D1, copied down...
=TEXT(A1,"dd/mm/yyyy")
Then in E1, copied down...
=DATE(MID(D1,7,IF(ISERROR(FIND(" ",A1,1)),4,2)),MID(D1,4,2),LEFT(D1,2))

Once you have them all converted, you can copy/paste values over your "dates" and then delete those 2 helpers

I tried this also but to no avail. Thanks for the help guys but I think this is a problem which can't be fixed. I'll just need to rebuild all the files. Thanks for your help.

Originally Posted by AlKey

Lets give it another try

=--IFERROR(TEXT(--TRIM(A1),"dd/mm/yyyy"),--MID(A1,4,2)&"/"&LEFT(A1,2)&TEXT(MID(A1,6,3),"yyyy"))

Hi AlKey,

I was having the same issue as TPar7322 and this formula works! Thanks!

However my A1 column also includes hh:mm:ss data that I also want to move over from the "A1" column. HOw would I modify the formula to carry over the data? Thanks!