uk us date problem
uk us date problem
(OP)
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.
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.





RE: uk us date problem
Bung
Life is non-linear...
RE: uk us date problem
prex
http://www.xcalcs.com
Online tools for structural design
RE: uk us date problem
=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))))