Hi,
I'm trying to convert data from an old to a new payroll system. One field contains multiple pairs of dates (start & finish, yy format) as follows:
Col A
Row1 89~92~95~97
Row2 ~~~90~94~
Row3 ~~81~90~~98~03~~~
The problem I have is that the new system needs dates in dd/mm/yy format, therefore, to import the above example it would have to appear as follows:
01/01/89~31/12/92~01/01/95~31/12/97
~~~01/01/90~31/12/94~
~~01/01/81~31/12/90~~01/01/98~31/12/03~~~
I need to maintain all the ~ (tilde) and put 01/01/ in front of the first number of the pair and 31/12/ in front of the second number in the pair. Unfortunately, there are over 20,000 entries in column A.
Has anyone any idea of how I would overcome this problem?
I'm trying to convert data from an old to a new payroll system. One field contains multiple pairs of dates (start & finish, yy format) as follows:
Col A
Row1 89~92~95~97
Row2 ~~~90~94~
Row3 ~~81~90~~98~03~~~
The problem I have is that the new system needs dates in dd/mm/yy format, therefore, to import the above example it would have to appear as follows:
01/01/89~31/12/92~01/01/95~31/12/97
~~~01/01/90~31/12/94~
~~01/01/81~31/12/90~~01/01/98~31/12/03~~~
I need to maintain all the ~ (tilde) and put 01/01/ in front of the first number of the pair and 31/12/ in front of the second number in the pair. Unfortunately, there are over 20,000 entries in column A.
Has anyone any idea of how I would overcome this problem?