| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change date format
Good day,
Thanks for all the help with my previouse queries, i am from South Africa so there is quite a time delay. I have two spread sheets and i copy a purchase order number from one to the other using a Vlookup, which works fine, however i now need to copy the date of the same order but require a different format. The original format is text 20041105 and i need it to be 05-Nov-04 in the new workSheet. The formula i am using is; ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)" Some help with some code would be appreciated. Thanks Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
Some details on the data would be useful, otherwise we are guessing.
To get the different date format, simply format the target cell as dd-mmm-yy -- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Good day, Thanks for all the help with my previouse queries, i am from South Africa so there is quite a time delay. I have two spread sheets and i copy a purchase order number from one to the other using a Vlookup, which works fine, however i now need to copy the date of the same order but require a different format. The original format is text 20041105 and i need it to be 05-Nov-04 in the new workSheet. The formula i am using is; ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)" Some help with some code would be appreciated. Thanks Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
Hello Bob,
The Part number is my look up value which i use to copy the Purchase order number over whith, i then want to copy the date over as mentioned before, this format is "General" and the cell it is going to is Formatted dd-MMM-yy. I have treid a manual vlookup but the date remains the same ? Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
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" wrote in message ... Hello Bob, The Part number is my look up value which i use to copy the Purchase order number over whith, i then want to copy the date over as mentioned before, this format is "General" and the cell it is going to is Formatted dd-MMM-yy. I have treid a manual vlookup but the date remains the same ? Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
Or even try
=--A1 or =A1+0 or =A1*1 -- HTH RP (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... 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" wrote in message ... Hello Bob, The Part number is my look up value which i use to copy the Purchase order number over whith, i then want to copy the date over as mentioned before, this format is "General" and the cell it is going to is Formatted dd-MMM-yy. I have treid a manual vlookup but the date remains the same ? Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
That would convert it to a number, but it still wouldn't be seen as a date
by Excel and therefore wouldn't format to the proper date. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Or even try =--A1 or =A1+0 or =A1*1 -- HTH RP (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... 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" wrote in message ... Hello Bob, The Part number is my look up value which i use to copy the Purchase order number over whith, i then want to copy the date over as mentioned before, this format is "General" and the cell it is going to is Formatted dd-MMM-yy. I have treid a manual vlookup but the date remains the same ? Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
But it would overcome the text date for his VBA which then formats the
activecell as a date, end requirement, and shows proper date style if the cell is formatted as a date.. Bob "Tom Ogilvy" wrote in message ... That would convert it to a number, but it still wouldn't be seen as a date by Excel and therefore wouldn't format to the proper date. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Or even try =--A1 or =A1+0 or =A1*1 -- HTH RP (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... 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" wrote in message ... Hello Bob, The Part number is my look up value which i use to copy the Purchase order number over whith, i then want to copy the date over as mentioned before, this format is "General" and the cell it is going to is Formatted dd-MMM-yy. I have treid a manual vlookup but the date remains the same ? Tempy *** Sent via Developersdex http://www.developersdex.com *** |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change date format
20041105
in a cell, whether stored as number or text will never format to 05-Nov-04 -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... But it would overcome the text date for his VBA which then formats the activecell as a date, end requirement, and shows proper date style if the cell is formatted as a date.. Bob "Tom Ogilvy" wrote in message ... That would convert it to a number, but it still wouldn't be seen as a date by Excel and therefore wouldn't format to the proper date. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Or even try =--A1 or =A1+0 or =A1*1 -- HTH RP (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... 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" wrote in message ... Hello Bob, The Part number is my look up value which i use to copy the Purchase order number over whith, i then want to copy the date over as mentioned before, this format is "General" and the cell it is going to is Formatted dd-MMM-yy. I have treid a manual vlookup but the date remains the same ? 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 | |||
Linear Mode
