Dates Displayed Wrong Issue.

I am using Excel 2010

In the Formula bar I show a date as 11/07/2013 (dd/mm/yyyy) format

In some cells it displays correctly as 11/07/2013
In other cells, it displays wrong as 07/11/2013 (this is just display) for my actual formulas and workbook statistics, it will read it correctly as dd/mm/yyyy format.


How can I ensure that through the entire workbook (20+ sheets) it will always display and be seen as the format I want (dd/mm/yyyy)

This excel workbook is being used on many computers, in Canada and the States, where computer date and time settings will be different.

Is a Marco needed or how I can format the workbook so it will keep this formatting even when moved around different computers?

Any help is appreciated,

Cheers,
Dan

Hi Dan

where are the dates being entered from? I wonder if the problem is because you are copying or inputting data from sources that use different date conventions. If so, formatting the cells may not help, as excel may have read the wrong date number into your cell.

Hi Nicky,

The dates are being entered in the cells, then once the return button is hit, it changes it.

Hi Dan
can you upload a copy for us to look at - or if the sheet is sensitive, just copy the date info into another one

...
or try this
right click on the columns with date and select format cells > date
pick English(Canada) from the drop-down menu under Locale (Location):

Thanks Nicky, the English(Canada) worked, only it changed all the dates that had been input and reversed them! GAH! time to manually change 3407 entires...

Hi Dan

Did you try Martin's advice and check if all the dates are numbers?

If you know which cells contain the "wrong" numbers, whether they are entered as text or numbers, it is possible to write a macro to convert them to the right date numbers. If you let me know whether they are numbers or text, or upload some examples, I will write one for you.