Formatting messed up dates that bled into another column

[schnarkle]

Hello,

I have a huge spreadsheet that mixed in date numbers from one column to another

Book1
	A	B
1	start date	end date
2	101319980	2212009
3	122920040	4182017
4	62220161	1022019
5	100920161	312019
6	91720171	2082017
7	110119961	82019
8	5292009	blank
Roster

Most of the time the last digit in A2 (0 or 1) needs to be moved to be the first digit in B2. However sometimes A2 is correct (with a blank ending date) as seen in the last row.

how can i fix this?

 

[yky]

Let's say the range of years is between 1990 and 2050, use the following two formulae.

=IF(AND(RIGHT(A2,4)+0>1990,RIGHT(A2,4)+0<2050),A2,LEFT(A2,LEN(A2)-1))
=IF(LEN(B2)=0,B2,IF(AND(RIGHT(A2,4)+0>1990,RIGHT(A2,4)+0<2050),B2,RIGHT(A2,1)&B2))

 

[schnarkle]

Brilliant! Thanks so much!