I've run into this issue. I have many files where the date that I need access to is formatted as a number in Excel. I did some research on the number and found that this number is the number of days that had passed since 1/1/1900.
With this information at the ready, I created a calculation that loked at the date to determine if it was a number. If it was a number, then it did a DateTimeAdd Function of 1/1/1900 + the number formatted date field from excel. Otherwise it just returned the date.
I quickly realized that almost all of my date fields were exactly 3 days off. So, I subtracted 3 days from the number formatted date field. This causes the date to be correct for 98% of my files. However, every so oftern(Particularly a file that should have had the date 12/1 and another that should have had the date 12/7 require that the subtraction be only 2 days, rather than 3.
If I can identify the cause, I could at least build an IF function for it, but even that makes me nervous unless I know the root issue. Obviously writing an excel macro to fix the date formats is possible, but I really would like it to be my last resort incase I run into this issue again.