Format data yyyy/mm/dd

[JudahRaion]

Good Afternoon,

I have this columns:

<nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr> <nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr><nobr></nobr>[TABLE="width: 268"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]20131008[/TD]
[TD="align: right"]20140108[/TD]
[/TR]
[TR]
[TD="align: right"]20131211[/TD]
[TD="align: right"]20140110[/TD]
[/TR]
[TR]
[TD="align: right"]20131211[/TD]
[TD="align: right"]20140110[/TD]
[/TR]
[TR]
[TD="align: right"]20131211[/TD]
[TD="align: right"]20140110[/TD]
[/TR]
[TR]
[TD="align: right"]20131211[/TD]
[TD="align: right"]20140110[/TD]
[/TR]
[TR]
[TD="align: right"]20131211[/TD]
[TD="align: right"]20140110[/TD]
[/TR]
[TR]
[TD="align: right"]20131213[/TD]
[TD="align: right"]20140113[/TD]
[/TR]
[TR]
[TD="align: right"]20131213[/TD]
[TD="align: right"]20140113[/TD]
[/TR]
</tbody>[/TABLE]

And I want to format them to yyyy/mm/dd and I tried this code:

Selection.NumberFormat = "yyyy/mm/dd"

But this don't work because the result is all time: ###############
And I can't see the date correctly.

The only way I can do this, is put cell to cell the "/" between year/month/day... in that way obviously excel accept, but I have a thousand of cells

Can you help me please?

Thank You

JD

 

[slinky]

You can't convert a non-date/time serial into one by simply applying formatting..

You would need to treat the value through left, right and mid to turn it into a real date.. For example, let's say that the first date is in A1..

=Right(A1,2)&"/"&Mid(A1,5,2)&"/"&Left(A1,4)

That would convert 20131008 into 08/10/2013

You could always do the same thing in VB to sprint through your thousands of values..

 

[Rick Rothstein]

As slinky said, you cannot format a number into a date, you must physically convert the value. This line of code will do that for the Selection...

Selection = Evaluate("IF(LEN(" & Selection.Address & "),TEXT(" & Selection.Address & ",""0000-00-00""),"""")")

 

[JudahRaion]

Thanks a lot. Both of you helped me with this question!

I will ask you two more questions for these.

Now I've other problem and I want ask your help too.

I've a google reminder for dates in one of my column and it works fine for all of actual dates.

But now I want to use that reminder for my client birthdays and for that I need to put that dates in 2014 date. How can I do that?

See the example:

Born Date	This Year date

<colgroup><col style="width: 78px"><col width="120"></colgroup><tbody>
[TD="align: right"]1978/08/03[/TD]
[TD="align: right"]2014/08/03[/TD]

[TD="align: right"]1948/09/02[/TD]

[TD="align: right"]1972/07/19[/TD]

[TD="align: right"]1935/04/19[/TD]

</tbody>

How can I find books about macros and vba so I can learn more do be like you, guys?

Thanks

Regards,

JD

 

[Rick Rothstein]

Thanks a lot. Both of you helped me with this question!

I will ask you two more questions for these.

Now I've other problem and I want ask your help too.

I've a google reminder for dates in one of my column and it works fine for all of actual dates.

But now I want to use that reminder for my client birthdays and for that I need to put that dates in 2014 date. How can I do that?

See the example:

Born Date	This Year date

<tbody>
[TD="align: right"]1978/08/03[/TD]
[TD="align: right"]2014/08/03[/TD]

[TD="align: right"]1948/09/02[/TD]

[TD="align: right"]1972/07/19[/TD]

[TD="align: right"]1935/04/19[/TD]

</tbody>

How can I find books about macros and vba so I can learn more do be like you, guys?

Assuming your dates start in cell A2, put this formula in cell B2 and copy it down to the bottom of your data...

=DATE(YEAR(NOW()),MONTH(A2),DAY(A2))

As for learning macros and VBA... there are hundreds of source online, so I would start by Googling the words Excel Macros VBA Tutorial.