I think you'd want a formula like this in D4:

=IF(A2<B2,DATE(YEAR(A2),DAY(A2),MONTH(A2)),A2)

Date format this cell as desired.

--
Jim Rech
Excel MVP
"Hari" wrote in message
...
| Hi,
|
| I have dates starting from row number 2 to row number 1500 of column A.
|
| This data was received from 2 to 3 different sources and different
| geographical region. Now half of them are in US date format (mm/dd/yy)
while
| rest are in Indian date format (dd/mm/yy)
|
| At the time of entering the data the same was not checked upon properly
and
| consequently some Manual calculations based on this data has turned out to
| be wrong due to incorrect interpretation.
|
| Now we have one more column of date data (in column B) which is absolutely
| correct and can be used for checking the column A date values. I mean the
| Column A has to be always greater than column B.
|
| In about 500 of the records the difference is coming to be negative. Now
in
| those records I have to interchange the month and date value. How do I do
| it.
|
| I have given some sample data. (Column C is basically a formula -- C2=
| if(A2<B2,"Not Fine", "Fine") (below I have converted all dates to
dd-mmm-yy
| to identify quickly)
|
| Column A Column B Column C
| Doubtful Date Correct Base date Check
| 7-Mar-03 6-Apr-03 Not Fine
| 12-Oct-03 6-Nov-03 Not Fine
| 5-Apr-03 12-Mar-03 Fine
| 10-Feb-03 19-Jan-03 Fine
|
|
| So in above case I would like to change A2 to 3-Jul-03 and A3 to 10-Dec-03
|
| Is there a way to do this using formula or macro (what would be the logic
of
| such a macro)?
|
| Formula wise I would like a solution where in Column D starting from row 2
I
| put something like an If condition which would check whether column C is
| "Not fine" and if so Interchange Month and Date of Column A otherwise if
| equal to "Fine" then equal to A. Problem is I dont now how to do such an
| interchange
|
| Please guide me.
|
| Regards,
| Hari
| India
|
|