how can i convert data to date format

[raveepoojari]

Deal all,

from long time i am faceing a problem to convert some data to date.

please have a look in my attachment. there i like to conver B columon to Date format how can i change.

[alansidman]

In an adjacent cell, type =datevalue(b2). This will give you the numerical value for b2. Now highlight b2 and select format. Select the type of date formatting you want. Copy B2 down. At this point you can either hide column b or replace it with the values in column C by highlighting column C. Copy-->Paste Special-->Values into column b. Then delete column C.

Alan

[TMS]

It looks as though you have American format dates. Excel is taking exception to "dates" that have a month greater than 12 and doesn't try to convert them. Sadly, where it thinks a date does look OK it does convert it but the assumption is that the day and the month are the wrong way around.

Try this in cell C2 and drag down:

Regards

[TMS]

@Alan: I don't think it will; at least, it doesn't for me. I get #VALUE!

Regards

[raveepoojari]

In an adjacent cell, type =datevalue(b2). This will give you the numerical value for b2. Now highlight b2 and select format. Select the type of date formatting you want. Copy B2 down. At this point you can either hide column b or replace it with the values in column C by highlighting column C. Copy-->Paste Special-->Values into column b. Then delete column C.

Alan

Thank You.

I did as u said..but i am getting Value Error some cell. now i attached tat one also..plz a have a look and do let me knw...how can i solove..

[raveepoojari]

It looks as though you have American format dates. Excel is taking exception to "dates" that have a month greater than 12 and doesn't try to convert them. Sadly, where it thinks a date does look OK it does convert it but the assumption is that the day and the month are the wrong way around.

Try this in cell C2 and drag down:

Regards

even i tried this also.....but its showing error #VALUE!

[TMS]

See the copy of your file with the dates converted. Maybe you can see how this works.

Regards

[TMS]

@raveepoojari:

I think that the problem is that I am seeing your data as presented in Excel 2003 in the UK and Alan is seeing your data as presented in the USA (Excel 2007?) so our solutions will depend on our locale.

Basically, some of the "dates" in the column will be "real" dates, that is, numbers and others will be text that look like dates. You need to differntiate between the two and take appropriate action.

Regards

[raveepoojari]

@raveepoojari:

I think that the problem is that I am seeing your data as presented in Excel 2003 in the UK and Alan is seeing your data as presented in the USA (Excel 2007?) so our solutions will depend on our locale.

Basically, some of the "dates" in the column will be "real" dates, that is, numbers and others will be text that look like dates. You need to differntiate between the two and take appropriate action.

Regards

Ur right....becoz i m using excel-2003 at ofc and using excel-2007 at home.
now wat can i do...?

[alansidman]

@TMShucks. Exactly what I am seeing. Locale will be a factor.

[TMS]

@raveepoojari:

The version of Excel that you are using shouldn't make a difference.

As a test, select all the data in column B. Format all the cells as General. If the "date" still "looks like a date", it's text, not really a date. If the cell has a number in it, that's a real date ... but it might have the day and the month the wrong way around ... admittedly, that's a guess based on the way I'm seeing your data here.

However, you do need to be sure that you get it right as it means the difference between 1st September and the 9th January. In my world, that looks like 01/09/2010 (dd/mm/yy) and 09/01/2010.

So, first check what you've got. Then do something to convert it. I think you should be able to use my formula as an example.

Regards

[alansidman]

Interestingly enough, I received a newsletter today from Francis Hayes ("The Excel Addict") and he covered this very issue. He had an interesting approach that works. I have attached for any that are interested in his solution.