Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
Hello
My PC is set up as UK region and hence displays dates as 20/01/06. However I have a spreadsheet sent from the US formatted as general and reflects 01/20/06 as the date. I am trying to use a vlookup where the dates match, I have tried format cells, date dd-mm-yy but this does not work it still shows 01/20/06. I have also tried to use datevalue, day, month, year functions (as a serial number) and then looking up those values. However they all return #value. Does anyone have any ideas how I can convert this date to UK date format? Any help is appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
Hi
Are the 'US dates' actual Excel dates? If you type =A2+1 (assuming the 'date' is in A2) does it give you 01/21/06? You could try selecting the column, go to Data/Text to columns and use the wizard. On the third page you can select (in the top right corner) that it is a date - and its format. Make sure you backup your data before you start. Hope this helps. Andy. "Nav" wrote in message ... Hello My PC is set up as UK region and hence displays dates as 20/01/06. However I have a spreadsheet sent from the US formatted as general and reflects 01/20/06 as the date. I am trying to use a vlookup where the dates match, I have tried format cells, date dd-mm-yy but this does not work it still shows 01/20/06. I have also tried to use datevalue, day, month, year functions (as a serial number) and then looking up those values. However they all return #value. Does anyone have any ideas how I can convert this date to UK date format? Any help is appreciated. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
Hello Andy
Thanks for your suggestions, but is does not appear to work. If I type =a2+1 then it still returns the #value. (if the day is more than 12) eg.01/20/05 = #value. However if the date is 1/12/05 then it returns 2nd Dec 05, whereas it should be 13th Jan 05. I also tried the Data/Text to col but it still has no effect. Is there anything further I can try. Thanks in advance. "Andy" wrote: Hi Are the 'US dates' actual Excel dates? If you type =A2+1 (assuming the 'date' is in A2) does it give you 01/21/06? You could try selecting the column, go to Data/Text to columns and use the wizard. On the third page you can select (in the top right corner) that it is a date - and its format. Make sure you backup your data before you start. Hope this helps. Andy. "Nav" wrote in message ... Hello My PC is set up as UK region and hence displays dates as 20/01/06. However I have a spreadsheet sent from the US formatted as general and reflects 01/20/06 as the date. I am trying to use a vlookup where the dates match, I have tried format cells, date dd-mm-yy but this does not work it still shows 01/20/06. I have also tried to use datevalue, day, month, year functions (as a serial number) and then looking up those values. However they all return #value. Does anyone have any ideas how I can convert this date to UK date format? Any help is appreciated. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
Nav
It sounds like there is a mix of dates Excel is interpreting those dates it can as standard UK dates the others it is leaving as text. I belive Andy's suggestion of using the Text to Columns will work if you follow the following steps. Backup the file before you start Select the original column of dates Data\Text to Columns Step1: delimited Step2: no delimiters selected Step3: Column Data Format as Date type MDY Finish This should convert all of the dates into Excel dates in the form you need. Please give it a go and let us know how you get on hth RES |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
Hi Robert
Thanks for your help, but it still doesn't work. I have taken the file onto 3 different PC's and tried it to no avail. I think I will have to try and get help writing a macro for this. Regards, Nav " wrote: Nav It sounds like there is a mix of dates Excel is interpreting those dates it can as standard UK dates the others it is leaving as text. I belive Andy's suggestion of using the Text to Columns will work if you follow the following steps. Backup the file before you start Select the original column of dates Data\Text to Columns Step1: delimited Step2: no delimiters selected Step3: Column Data Format as Date type MDY Finish This should convert all of the dates into Excel dates in the form you need. Please give it a go and let us know how you get on hth RES |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
You could try a formula in another column, e.g. if your dates are in column A use this in B1 and copy down =IF(ISTEXT(A1),VALUE(REPLACE(MID(A1,4,3)&A1,7,3,"" )),DATE(YEAR(A1),DAY(A1),MONTH(A1))) If the "date" is of the form 01/20/05 and excel is treating it as text it will convert to a date, i.e. 20/01/05 or if it's being treated as a date but the wrong one - e.g. 10th december becomes 12th October - it will switch the month and day. Note: this is designed to work where the days and months are always shown as two digits e.g. 01 not just 1 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=503297 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
...another way would be to change your regional settings to US - import the file and then change your settings back -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=503297 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Datevlaue function/ day/month/year function
Thanks for your help, this works a real treat.
Regards, Nav "daddylonglegs" wrote: You could try a formula in another column, e.g. if your dates are in column A use this in B1 and copy down =IF(ISTEXT(A1),VALUE(REPLACE(MID(A1,4,3)&A1,7,3,"" )),DATE(YEAR(A1),DAY(A1),MONTH(A1))) If the "date" is of the form 01/20/05 and excel is treating it as text it will convert to a date, i.e. 20/01/05 or if it's being treated as a date but the wrong one - e.g. 10th december becomes 12th October - it will switch the month and day. Note: this is designed to work where the days and months are always shown as two digits e.g. 01 not just 1 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=503297 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |