Date Format

[kumar0318]

Hello, Could you please help me with formatting date from 20231215 to MM/DD/YYYY format?

 

[6StringJazzer]

The format you have is numeric and Excel cannot recognize it as a date. You will have to use a formula to convert it. If your date is in A1:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Then use Format Cell to display as desired.

 

[kumar0318]

Thank you, I have some dates in the correct format in the same collum, how do I keep them unchanged from above formula?

 

[6StringJazzer]

Hard to say for certain without sample data. I think this:

=IF(A1<400000,A1,DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)))

 

[Alex Blakenburg]

Thank you, I have some dates in the correct format in the same collum, how do I keep them unchanged from above formula?

As Jeff mentioned it is hard to advise without seeing the data.
Typically when some convert and some don't the ones that seem to have converted will have had their day and month reversed. (the day portion will have been < 13 allowing excel to put it in the month position)
You might want to check 1 or 2 of the ones that converted to see if that was the case.

 

[kumar0318]

Here is the sample data from the column. As you can see, there are text, N/A and dates. I would like to convert the date which is in wrong format (20210112) and everything else remain as is in the formula.

Row Labels

20210112

N/A

Not Paid as of 1/6/2025

Offset

Paid in 2019

12/24/2020

1/7/2021

 

[Cubist]

Assuming all of your data are text. Maybe this.

Book1
	A	B
1	Row labels	formatted m/d/yy
2	20210112	1/12/21
3	N/A	N/A
4	Not Paid as of 1/6/2025	Not Paid as of 1/6/2025
5	Offset	Offset
6	Paid in 2019	Paid in 2019
7	12/24/2020	12/24/20
8	1/7/2021	1/7/21
Sheet5

Cell Formulas
Range	Formula
B2:B8	B2	=LET(a,A2:A8,IF(ISERROR(--a),a,IF(ISNUMBER(SEARCH("/",a))*ISNUMBER(--a),--a,DATE(LEFT(a,4),MID(a,5,2),RIGHT(a,2)))))
Dynamic array formulas.

 

[kumar0318]

This works, thank you! Just realized that I also have zeros in the same column as you can see second last cell from bottom. Can you please revise the formula to include this instance as well?

Clearing Date
N/A	N/A
Offset	Offset
Offset	Offset
Offset	Offset
N/A	N/A
Offset	Offset
Offset	Offset
Offset	Offset
Offset	Offset
N/A	N/A
Offset	Offset
00000000	#NUM!
20240109	1/9/2024

 

[kumar0318]

Also when I ran with as is formula having my data in column A, it works fine but my actual column is Z so I replaced "a" and "A" with z and I got this result.

Clearing Date	Clearing date
N/A	#SPILL!
Offset	#SPILL!
Offset	#SPILL!
Offset	#SPILL!
N/A	#SPILL!

 

[Cubist]

It's a spill formula so insert the first cell only, clear out things below. Adjust the range inside the formula don't copy down. Updated for '00000000' entries:

=LET(a,A2:A8,IF(ISERROR(--a),a,IF(ISNUMBER(SEARCH("/",a))*ISNUMBER(--a),--a,IFERROR(DATE(LEFT(a,4),MID(a,5,2),RIGHT(a,2)),a))))

Book1
	A	B	C
1	Row labels	formatted m/d/yy
2	20210112	1/12/21	<-Insert formula here only
3	N/A	N/A
4	Not Paid as of 1/6/2025	Not Paid as of 1/6/2025
5	Offset	Offset
6	00000000	00000000
7	12/24/2020	12/24/20
8	1/7/2021	1/7/21
Sheet5

Cell Formulas
Range	Formula
B2:B8	B2	=LET(a,A2:A8,IF(ISERROR(--a),a,IF(ISNUMBER(SEARCH("/",a))*ISNUMBER(--a),--a,IFERROR(DATE(LEFT(a,4),MID(a,5,2),RIGHT(a,2)),a))))
Dynamic array formulas.