Change date format

T

Tempy

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
 
B

Bob Phillips

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)
 
T

Tempy

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
 
T

Tom Ogilvy

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"
 
B

Bob Phillips

Or even try

=--A1

or

=A1+0

or

=A1*1

--

HTH

RP
(remove nothere from the email address if mailing direct)


Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
Or even try

=--A1

or

=A1+0

or

=A1*1

--

HTH

RP
(remove nothere from the email address if mailing direct)


Tom Ogilvy said:
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
Click to expand...
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
Or even try

=--A1

or

=A1+0

or

=A1*1

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
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
Click to expand...
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


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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

20041105

in a cell, whether stored as number or text will never format to

05-Nov-04

--
Regards,
Tom Ogilvy


Bob Phillips said:
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 said:
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 said:
Or even try

=--A1

or

=A1+0

or

=A1*1

--

HTH

RP
(remove nothere from the email address if mailing direct)


That is because the date isn't being stored as a date I would
Click to expand...
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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change Date format 2
Tom Ogilvy - More help please 4
Do within an If statement 3
Advanced Filter works in recorder but not while running macro 5
File Name changes with dates and Deleting Rows of Zeros 0
macro - copy, paste, convert column data 3
Month & Year Text to Date Format - Help Please 3
Strange vlookup results 17

Top