Convert text mm-dd-yy date from external file & compare to TODAY()

I have a source xls file with a list of due dates down a column. The date is entered as text in the format 'mm-dd-yyyy.
Consider this file read only; I do not want the user having to format anything - it's plug-and-play!

The target xlsx file (must run on excel 2003) has a column of formulas using INDIRECT and OFFSET to find each of the source file dates and compare them to today's date.
For example: F2=AND(Logical1, Logical2, OFFSET(INDIRECT(filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1)>=TODAY())

Where C1 is the desired source column (found by header name using MATCH, then formatted into letters using ADDRESS)
G2:G500 are static values for the row offset, from 0 to 49.
F2:F500 will contain True if the "Logical1/2" conditions are True and the date from the source file in the corresponding row is on or after today's date.

So here's the scenario: I'm pulling a date from the source file. Let's say it's '02-11-2014 (February 11 2014). That works fine with
=OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1)
Now I need to convert it to an actual date, but I haven't been able to achieve this with DATEVALUE or TEXT:
=--TEXT(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1),"mm-dd-yyyy")
=DATEVALUE(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1))
I get #VALUE
Maybe I need to change the dashes (-) to slashes (/)
=DATEVALUE(SUBSTITUTE(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1),"-","/"))
Nope. Doesn't work. That's the first problem.
But let's assume I got the formula right and it returned a proper date number.
Did it return the date as Feb 11 2014, or Nov 2 2014? That's the second problem.
Beyond avoiding MM and DD mixup, I need to get the date into the same format as the regional date format for whatever computer is running the spreadsheet.
I use dd/mm/yyyy, but someone else might use mm/dd/yyyy or something else.
I'm assuming if I manage to do the above using DATEVALUE it will internally convert it to a universal date serial number. Is that right?

In summary, I need to reference another file, pull a value from a cell, format it, change it to a date number, and compare it to TODAY(). All in one single formula.
I can't format the source file. I also don't want this spreadsheet to turn into an 8MB behemoth. So I don't want to be importing all the due dates from multiple files into multiple sheets, operating on them through multiple steps, creating more columns with more formulas and more data just to get a true or false, for each date.
I'd also like to avoid things like this monster running down 500 rows in 14 different sheets if possible:
=AND(ISBLANK(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C2&"2"),G2,0,1,1)), OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C3&"2"),G2,0,1,1)>0, DATEVALUE(MID(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1),4,2)&"/" &LEFT(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1),2)&"/" &RIGHT(OFFSET(INDIRECT("'[filename.xls]"&"sheetname'!"&C1&"2"),G2,0,1,1),4))>=TODAY())
That repeated 7000 times across the 14 sheets would be 2.8MB just for that formula if I'm counting right.

Thanks for your time and any suggestions, insight, or links you can provide.

This is the biggest and foremost problem that MUST be addressed first.

Originally Posted by Frontier

I use dd/mm/yyyy, but someone else might use mm/dd/yyyy or something else.

Inconsistency in source data is the root of all evil in Excel.

If the dates are entered as text
And you can't be certain if a person entered it as mm/dd or dd/mm
Then neither can Excel.
If the date is ambiguous (both day and month are 12 or less)
It will be impossible for excel to know which is which.

There must be a standard in the source of dates in either mm/dd or dd/mm format.

Thank you for your replies.

Originally Posted by FDibbins

1st, INDIRECT only works on open workbooks (just so you are aware)
2nd, if your dates are already in text format, TEXT() wont help, becaue TEXT() converts numbers TO text.
3rd, seeing as your dates are already text, you are probably going to need to manually convert some of them, unless you can say for sure that the ALL of the 1st set of values are months OR the ALL of the 1st set of numbers are days. If you cannot be certain of that, excel wont be able to decide wither...is 6/7/13 6 July or 7 June


To best show what you are working with though, please upload a sample workbook, showing before and after

1) Yep, I know. I wish I knew how to not depend on an open file without VBA, like I originally did in OpenOffice, but Excel doesn't seem to know how to do that.
2) You'll notice my formula using TEXT had a double negative, which should convert the text to a negative number then to a positive number.
3)
a) Turns out the dates in the source file are NOT text. They are not proceeded by a single-quote '. They are in fact "General" number format. Because the cells were not generated in Excel, but by our database, they retain their original "mm-dd-yyyy" format without autoformatting by Excel. If those cells are edited, any date where both mm and dd are <13 becomes dd/mm/yyyy (dd and mm switched). Dates such as 06-25-2014 are not recognized and left just as they are.
b) The source file is consistent; ALL dates are ALWAYS mm-dd-yyyy. I'm sorry if I didn't make this clear.
The issues is that my computer's regional date settings (which Excel uses) are dd/mm/yyyy, and someone else using this spreadsheet might have their computer set for mm/dd/yyyy, or yyyy/mm/dd, or whatever. So when I reference dates in the file, Excel will try to automatically convert dates it "recognizes" (month & day <13) into the native date format or a corresponding date serial number, even if that conversion is wrong, while other dates will be treated as a non-date format number. Then I'll need to compare both the dates (wrong dates on my computer!) and the non-date numbers to TODAY().

I have included two files to show what I'm doing.

The first is the Source file with each person's info. NOTE: The dates are entered as TEXT here with a single quote because I cannot edit the cell to show "##-##-####" instead of "##/##/####" any other way if mm and dd <13. The real source is always always always mm-dd-yyyy in "general" number format.

The second is the Target file which pulls data from Source into printable boxes. The purple cells find which rows contain valid entries (Program="This", Completed=blank, and Due by date not elapsed). Green cells display value entries. The valid entries are pulled into boxes corresponding to the person they belong to.

You can see the the first source entry for person 34411 is being passed TRUE while the second one is FALSE. It should be the other way around.

Originally Posted by Jonmo1

This is the biggest and foremost problem that MUST be addressed first.
Inconsistency in source data is the root of all evil in Excel.
...
There must be a standard in the source of dates in either mm/dd or dd/mm format.

Not an issue. Please see above.

I have tried copying the Source file dates into a column in Target file using references. Then, after switching my computer to US regional time and the short date to MM/dd/yyyy, I used format cells in Target file to set them to Date: *dd/mm/yyyy. After doing this, using =CELL("format",target_cell_ref) to test the formatted cells displays "D4" - the code for a m/dd/yyyy date. But the cell contents still display the date left-aligned and unchanged, which to me indicates they are seen as text and not recognized, and the "D4" is only telling me the format of the cell and not the content (which makes sense).

The asterisk in the date format should allow compatibility if I change my computer's region back to Canada with dd/MM/yyyy short dates. But this obviously isn't working either.

I've decided to concede to using MID(), LEFT(),RIGHT() to convert the mm-dd-yyyy date to dd/mm-yyyy if I have to. But this still doesn't solve the problem because some users of this workbook may then have compatible dd-mm-yyyy settings, but others may actually use US format and we're back to square one.

It seems absolutely ridiculous that there's no way to turn off autoformat in Excel, and agree with someone said in a reply to another question that this is arrogant of Microsoft not to incorporate such an easy to implement option. But I think the fundamental problem here is that Excel uses regional date settings from each computer to deal with dates. This may make sense for most people and tasks, but it breaks everything for me and I can't find a workaround.

Yet many people around the world work with international clients. So there must be a solution other than programming custom spreadsheet software!

I'm not experienced with working with Excel, and don't have all that much experience with spreadsheets in general. I've tried a whole lot of other things too, but nothing has worked. And I can't find any solutions online that do not require changing my Source file. I really need some help here, please. Thank you.