Aidan is obviously entirely correct, but I thought I'd add a few bullets to
help out a bit as well:
1) One pretty quick way of checking whether Excel has recognised that the
contents of a cell is in fact a date rather than a generic text string is to
look at how the cell contents have been justified: by default, Excel
left-justifies generic text, but normally right-justifies dates. However, if
the cell justification has been set manually, then this simple check will
obviously not tell you anything at all.
2) You can easily right-click on a cell to bring up the cell context menu,
and then select the Format Cells... menu item. In the Format Cells dialogue,
check the Category setting of the Number tab. If it says Date for the cells
you're checking then you're lucky, and if it says General you've got a bit of
work to do.
3) Of course, you can put together a VBA method that iterates over all cells
of all sheets, and if the data entered in a particular cell looks like an
American date ("mm/dd/yyyy") but due to some reason or another is not
properly recognised as a date, you could let the macro patch it up.
4) However, if the people editing this workbook have indeed put US formatted
dates into the worksheets, and if they were indeed using US settings, then
everything should be all right.
5) Ideally, you should NOT try to change the date formats of cells manually
-- let Excel handle it, and just make sure your users know that they are
running the system with US or UK settings. In theory, everything should work
transparently and no one should have to bother about changing date formats
explicitly. Check your "Regional and Language Options" of your Windows
system, select the Regional Settings tab, and make the necessary adjustments
(English US or UK). It Should Just Work (tm)
Hope this helps,
/MP
