uk us date problem

[etch]

I have a challenge with a spreadsheet. We have a spreadsheet to record pass/fail data on a daily basis, now for some reason the computer was set up to US keyboard so tha the dates are appear on his screen as dd/mm/yy . when we open the sheet on another computer and try to analyse the data, we get them posted as mm/dd/yy. This makes pivot tables impossible.

the operator is not too experieced with computers, and has enteredn some dates in general text, date text as mm/dd/yy and as date text as dd/mm/yy.

I think it might be to do with uk or US keyboard setup originally. Is there anyway in excel to change the dates in all cells to read dd/mm/yy.

 

[Bung]

It's not the keyboard, it's the set-up of your spreadsheet and the default options for things like currency, date, how negatives are displayed, etc. It's in one of the setup options somewhere on the menu bar of the spreadsheet.
Bung
Life is non-linear...

 

[prex]

The choice is in the general Windows setup (Control panel -> International setup): there you can choose the general format for dates, that Excel will use as a default, if a local cell format is not specified. prex

http://www.xcalcs.com

Online tools for structural design

 

[TerryFoley]

I had the same problem I wanted dates from format mm/dd/yy (or even mm/dd/yyyy) to be converted to dd/mm/yy. I eventually used this little gem I wrote. (Assume Cell D23 in the example has a date of the above format and you are setup to date format dd/mm/yyyy as default on windows..)

=IF(ISTEXT(D23),DATEVALUE(CONCATENATE(
MID(D23,FIND("/",D23)+1,FIND("/",D23,FIND("/",D23)+1)-FIND("/",D23)-1),"/",
LEFT(D23,FIND("/",D23)-1),"/",
RIGHT(D23,LEN(D23)-FIND("/",D23,FIND("/",D23)+1)
))),DATEVALUE(CONCATENATE(MONTH(D23),"/",DAY(D23),"/",YEAR(D23))))