Excel Date Format issue

[Plutark]

Hi,

We have clients sending us daily prices for financial securities. The issue is that one provider keeps messing the format all the time. Is there a way, or a formula they could insert in the file to force the format.

for example they would change from 12/31/2011 to 31/12/2011 or 31-12-2011 or 12-31-2011
they can be really creative with that, they just do not seem to care either that we need the same format. So I was wondering if there was any way to fix that?

[npamcpp]

That's a tough one. Up to the 12th of every month, it is really impossible to tell if the month or the day is first in the date order, so it is really hard to check if 12/09/2012 is meant to be Sep-12 or Dec-9. If a provider of data with dates does not understand this issue, it may be time to educate them. Pay their invoice/benefit/bonus/interest, whatever, on Dec-9 instead of Sep-12 and see if they complain. Maybe they'll "get" the difference then.

[Plutark]

hahaha - good one. Yes that's the most annoying thing with Excel, this date system too easy to mess up. I'll try explaining them for the millionth time. Thanks for the quick feedback

[TMS]

If it is a spreadsheet that you send out for completion, you could maybe use Data Validation to restrict the cells to valid dates. That wouldn't stop them copying and pasting over the DV cells or getting the low days and months wrong. Perhaps a text box somewhere explaining the impact of getting the dates wrong ...

Regards, TMS