Excel Formula Date

[tropics123]

Hello, I'm hoping someone will have a good solution for this. Our client sends a list of dates and it either has seven or eight digits in column M, depending on the month. The format we want is MM/DD/YYYY.
The formula I have is: =LEFT(RIGHT(M2,8),2)&"/"&LEFT(RIGHT(M2,6),2)&"/"&RIGHT(M2,4)

However, the issue is when the month is a single digit such as May, then it throws off the formula. Please help!

Column M	Column N
Client's Date	Convert Date
1062020	10/06/2020
12042017	12/04/2017
12052019	12/05/2019
5012017	50/01/2017
6012007	60/01/2007

 

[Fluff]

How about

ZFluff.xlsm
	M	N
1	Client's Date
2	1062020	06 January 2020
3	12042017	04 December 2017
4	12052019	05 December 2019
5	5012017	01 May 2017
6	6012007	01 June 2007
Model

Cell Formulas
Range	Formula
N2:N6	N2	=DATE(RIGHT(M2,4),LEFT(M2,LEN(M2)-6),MID(M2,LEN(M2)-5,2))

 

[Toadstool]

Assuming your Client's Date is in mddyy format then to get your required MM/DD/YYYY

Book1
	M	N
1	Client's Date	MM/DD/YYYY
2	1062020	01/06/2020
3	12042017	12/04/2017
4	12052019	12/05/2019
5	5012017	05/01/2017
6	6012007	06/01/2007
Sheet4

Cell Formulas
Range	Formula
N2:N6	N2	=IF(LEN(M2)=7,"0"&LEFT(M2,1),LEFT(M2,2))&"/"&LEFT(RIGHT(M2,6),2)&"/"&RIGHT(M2,4)

 

[tropics123]

Thank you Toadstool! It worked perfectly!

Assuming your Client's Date is in mddyy format then to get your required MM/DD/YYYY

Book1
	M	N
1	Client's Date	MM/DD/YYYY
2	1062020	01/06/2020
3	12042017	12/04/2017
4	12052019	12/05/2019
5	5012017	05/01/2017
6	6012007	06/01/2007
Sheet4

Cell Formulas
Range	Formula
N2:N6	N2	=IF(LEN(M2)=7,"0"&LEFT(M2,1),LEFT(M2,2))&"/"&LEFT(RIGHT(M2,6),2)&"/"&RIGHT(M2,4)