Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
US and Indian Date conflict
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
US and Indian Date conflict
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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Symbol for Indian Rupee ( रू ) | Excel Discussion (Misc queries) | |||
Indian Currency add | Excel Discussion (Misc queries) | |||
Formatting for Indian Rupees | Excel Worksheet Functions | |||
comma in indian style | Excel Discussion (Misc queries) |