Date format - Question

[J Lowe]

I am needing to turn 0102 into 01/02 . I have thousands of these that I need to convert.

Is there an easy way to do this?

Thank you,
Jason

PS: The new format is great! Maybe one day, I can make more of a contribution to the message board instead of always having to ask for help.

 

[J Lowe]

Well, day two.....

I stopped working on the problem yesterday because MS keeps saying that I have created a circular reference. It does, however, change 1191 to 1/0/1900, which tells me I am on the right track.

Can anyone suggest why I might be getting these circular references and how to get it to change it to a format that I can use?

This is the formula that I am pasting over the data:

=DATE(RIGHT(G2,2),LEFT(G2,2),1)

I have, by the way, followed all of the suggestions in this posting and really appreciate all of the assistance I have received. This board is a great tool for me.

Thanks,
Jason

 

[Barrie Davidson]

On 2002-02-19 08:35, J Lowe wrote:
Well, day two.....

I stopped working on the problem yesterday because MS keeps saying that I have created a circular reference. It does, however, change 1191 to 1/0/1900, which tells me I am on the right track.

Can anyone suggest why I might be getting these circular references and how to get it to change it to a format that I can use?

This is the formula that I am pasting over the data:

=DATE(RIGHT(G2,2),LEFT(G2,2),1)

I have, by the way, followed all of the suggestions in this posting and really appreciate all of the assistance I have received. This board is a great tool for me.

Thanks,
Jason

Try Paste|Special|Values when you are pasting over your data. My guess is that your data is in G2, that creates your circular reference.

 

[J Lowe]

Barrie,

That was exactly what I was doing. I was trying to paste the information over the data that was in the cells, which, apparantly, is a no-no.

After setting the formula in a blank cell in another column, I have been able to Paste/Special the formula as Text, then choose Format Cells/Custom/Type/mmdd, which puts the numbers in the format I need. From there, I can drag the formula down and it will make the necessary changes to the entire column that I need changed. After I finish that, I can cut and paste over the old column.

Once again, thanks to everyone on the board for helping me with this issue. I am learning so much here.

Gracias,
Jason

 

[J Lowe]

One more addendum to my last reply.

I found that if I were to cut and paste the new column over the old column, I go back to the old problem of the circular reference. What I decided to do instead is just to have a new column.