Change date format

  • Thread starter Thread starter Tempy
  • Start date Start date
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
 
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)
 
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
 
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"
 
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...
 
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...
 
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...
 
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...
 
Back
Top