Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Date format
Hi Tom,
You gave me the code below for a previous query, i have an entire column (K) which needs the format changing and copying, i use a loop with a vlookup, it could be anything from 10 to 5000 rows. That is because the date isn't being stored as a date I would suspect - it is being stored as a number or as text. You would have to break it into pieces and make it a date assume the value is in A1 =DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2)) would be the basic formula. You now need to replace A1 with the location of the cell ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)" sStr = "VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)" sStr1 = "=DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2))" sStr1 = Replace(sStr1,"A1",sStr1) ActiveCell.FormulaR1C1 = sStr1 ActiveCell.NumberFormat = "dd-mmm-yy" -- Regards, Tom Ogivy Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Date format
if vlookup is returning a cell reference, then
sStr1 = Replace(sStr1,"A1",sStr1) should be sStr1 = Replace(sStr1,"A1",sStr) Other than that, I don't know what you are asking. -- Regards, Tom Ogilvy "Tempy" wrote in message ... Hi Tom, You gave me the code below for a previous query, i have an entire column (K) which needs the format changing and copying, i use a loop with a vlookup, it could be anything from 10 to 5000 rows. That is because the date isn't being stored as a date I would suspect - it is being stored as a number or as text. You would have to break it into pieces and make it a date assume the value is in A1 =DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2)) would be the basic formula. You now need to replace A1 with the location of the cell ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)" sStr = "VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)" sStr1 = "=DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2))" sStr1 = Replace(sStr1,"A1",sStr1) ActiveCell.FormulaR1C1 = sStr1 ActiveCell.NumberFormat = "dd-mmm-yy" -- Regards, Tom Ogivy Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Date format
Hi Tom,
I am looping down a spreadsheet for anything between 10 and 5000 rows copying from one spreadsheet to another using the vlookup to insert the changed date into the new spreadsheet. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |