US and Indian Date conflict

[Hari]

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

 

[Jim Rech]

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
| 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
|
|

 

[Tom Ogilvy]

=if(C2="Fine",A2,DateValue(mid(A2,2)&"/"&Left(A2,2)&"/"&Right(A2,2))