Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
US dates to UK??
I have an Excel 2003 spreadsheet that has a column full of dates. I have no control over how the spreadsheet has been configured. The dates are in an American format but I am in the UK and my laptop is configured for uk dates. I have noticed that cells that contain dates that would be illegal in uk format i.e. '10/29/04 10:36' are formatted 'General' Dates that are legal in the uk format i.e. 12/8/05 11:23 are formatted 'Custom dd/mm/yyyy hh:mm'. I have failed to get the dates to read in the uk format i.e. using the above examples:- 10/29/04 needs to be 20 April 2004 and 12/8/05 needs to be 8 December 2005. In the first example I cannot extract a serial because it is seen as text. In the second any conversion is coming out as 12 August 2005. I am not worried about the time, I just need the date. I know that I am probably missing something silly here but ban anyone give me a pointer on how to get these dates to read as I need please. Regards. Bill. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
US dates to UK??
Try: =TEXT(A1,"dd mmmm yyyy") -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=491783 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
US dates to UK??
Hi Bill,
It looks like the dates were already no "real" Excel dates; otherwise they would have been converted (not really, shown actually) as UK dates. Did you import the dates? If so, probably as a .csv file. Better do that as a .txt file; that gives you the opportunity to tell Excel what type of dates they are Your example: 10/29/04 needs to be 20 April 2004 was probably not right? -- Kind regards, Niek Otten "Bill" wrote in message .. . I have an Excel 2003 spreadsheet that has a column full of dates. I have no control over how the spreadsheet has been configured. The dates are in an American format but I am in the UK and my laptop is configured for uk dates. I have noticed that cells that contain dates that would be illegal in uk format i.e. '10/29/04 10:36' are formatted 'General' Dates that are legal in the uk format i.e. 12/8/05 11:23 are formatted 'Custom dd/mm/yyyy hh:mm'. I have failed to get the dates to read in the uk format i.e. using the above examples:- 10/29/04 needs to be 20 April 2004 and 12/8/05 needs to be 8 December 2005. In the first example I cannot extract a serial because it is seen as text. In the second any conversion is coming out as 12 August 2005. I am not worried about the time, I just need the date. I know that I am probably missing something silly here but ban anyone give me a pointer on how to get these dates to read as I need please. Regards. Bill. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
US dates to UK??
Sorry Niek, the 20 April 2004 was a complete mess up, it should read 29
October 2004. You are right about the csv file element. I have a downloaded vbs script to convert a log file that is in a very poor format to a csv file that can be opened and in Excel and far better formatted. Hence I cannot change how it is doing things. Will try renaming the file as txt as you suggest and see what happens. Regards. Bill. "Niek Otten" wrote in message ... Hi Bill, It looks like the dates were already no "real" Excel dates; otherwise they would have been converted (not really, shown actually) as UK dates. Did you import the dates? If so, probably as a .csv file. Better do that as a .txt file; that gives you the opportunity to tell Excel what type of dates they are Your example: 10/29/04 needs to be 20 April 2004 was probably not right? -- Kind regards, Niek Otten Bill. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
US dates to UK??
Bill,
if your date/times are imported as text, then the following formula will do (assuming dates are past Y2K). =DATE(VALUE(20&MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,FIND(" ",A1)-FIND("/",A1,FIND("/",A1)+1)-1)),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)) HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |