columns D and AD in sheet 1 are formatted as text. You should format these columns as date, and if necessary, reenter data, because in AD1, there is a text: "Nov-09" !! this is just a piece of text to excel, cannot recognize it as a date...There is no way excel can recognize it as 11/3/2009...( Nov can be recognized as month 11, but the day does not appear in that text ). in you file sheet1 reatached, i reformatted columns D and AD, and reentered 11/3/2009 in AD1. You should have no errors now..



tell me if you need further assistance in automatic reformatting and reentering existing data if you have a large number of rows...

Attached Files

COMPARE.zip (34.6 KB, 9 views)

That file looks fine
Thanks a lot for dat..

1. Now, how did you re format the field. (i really appreciate a step by step procedure from you) Becoz am not an expert in excel. I tried right click and format but it is not getting effected.


2. As you mentioned i have very large number of rows(4000). Reentering will be difficult task.

so, how do i do it.

check this link for instructions on how to set up your first macro...
http://office.microsoft.com/en-us/ex...138.aspx?CTT=1

repeat the operations in video, after you stop the macro, press visual basic button as picture attached, and replace your newly created macro with this macro: (your visual basic window should look like in picture attached...) Next step is to press the little green triangle highlighted in red in picture attached, it will do your job in less than 30 seconds. But attention, if you have wrong data in AD column, as i posted before, ( remember: "AD1, there is a text: "Nov-09" !! this is just a piece of text to excel, cannot recognize it as a date...There is no way excel can recognize it as 11/3/2009" )...you have to reenter data in those cells. Good luck !

Sub RecalculateCells()
Dim i As Integer
Application.ScreenUpdating = False

For i = 1 To 1000 'if you need more than 1000 rows, change here
ActiveSheet.Cells(i, 4).Select '4 means column D
Selection.NumberFormat = "mm/dd/yyyy" 'choose format for D column
ActiveCell.Value = ActiveCell.Value ' recalculate cells selected
Next i

For i = 1 To 1000 'if you need more than 1000 rows, change here
ActiveSheet.Cells(i, 30).Select '30 means column AD
Selection.NumberFormat = "mm/dd/yyyy" 'choose format for D column
ActiveCell.Value = ActiveCell.Value ' recalculate cells selected
Next i

ActiveSheet.Cells(1, 4).Activate
Application.ScreenUpdating = True
End Sub

forgot to attach picture

Attached Images

Run macro.jpg (99.0 KB, 10 views)

Hi!

Actually am formatting a date for example:

November 05, 2009 to Nov-09

not vise versa or

Nov-09 to 11/05/2009

Anyways letme check with the directions from you and get back...

Thank you Catalin

Hi! Catalin

I have attached the files.

In that Field AD of file named 'Typed version' Its manually typed as : Nov-09 and the formula bar also displays the same(Nov-09). Its formatted as Text.

In that Field AD of file named 'Converted version' Its posted from MS word(copy paste): November 03, 2009 and formatted as Mmm/yy and the formula bar displays as 11/3/2009. Its formatted as Text.

What i need is the AD field of file 'converted version' to display the same i.e. Jan-09 (Mmm-09). and the formula bar should also display the same. (Its not possible to re-write its since they are tooo large in numbers)but I dont mind what the formatting is. sothat when i take both these files to Comparison Software Both fields will be the same and it will return No Error.

I have attached a comparison file result were the dates are same but it shows error just because both are in diff format.

Please refer to the AD field of Comparison file.

Please provide me with a good remedy.....

Attached Files

Compare1.zip (34.8 KB, 10 views)

Sorry i mentioned Jan-09 for Nov-09.............sorry dont misunderstand

since the AD column in the sheet "typed " cannot be formatted to compare it to a date, the only thing i can advice you to do is to change column AD data in which date is entered correctly, to a piece of text that can be compared to the text in typed sheet: in an empty column, say AF, enter this formula: =TEXT(AD1;"mmm-yy"); copy this formula down to your row range; then click on column letter AF to select the entire column AF , click on AD1, and paste only values( paste special- only values). In this moment, AD1 column can be compared... look at sample attached..
Hope this fits you

Attached Files

Converted version.xlsx (33.9 KB, 9 views)

Yes Catalin..... its works......... dat's enough for me......
Thanks a lot...... .......

You're welcome