Help with Formula Year, Month and date

[zone709]

HI,
I was trying to put the right formula together to change Month, Day, Year to Year, Month and day.

Example in C1 I have 120721. Need to change it to 211207 - Year, Month and day

 

[Fluff]

How about

=REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2))+0

 

[zone709]

Thank you i wasn't even close.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Micron]

If you don't mind me asking, what is the +0 for? I tested without and got the same result. Still trying to understand how Replace in Excel works!

 

[Fluff]

The replace function returns a text string & the +0 converts that back to a number.

 

[Micron]

Thanks for responding and teaching. Would that be the same (but perhaps easier) than
VALUE(REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2)))
Formatting either to a date just happens to return the same date.

 

[Peter_SSs]

Another way (though I am not sure the OP needs conversion to number?)

=--MID(C1&C1,5,6)

 

[jtakw]

Thanks for responding and teaching. Would that be the same (but perhaps easier) than
VALUE(REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2)))
Formatting either to a date just happens to return the same date.

Hi,

Yes, that would be the same, just a different method, the following all do the same:

Book3.xlsx
	C	D
1	120721	211207
2		211207
3		211207
4		211207
5		211207
6		211207
Sheet1011

Cell Formulas
Range	Formula
D1	D1	=REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2))+0
D2	D2	=0+REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2))
D3	D3	=VALUE(REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2)))
D4	D4	=REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2))*1
D5	D5	=1*REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2))
D6	D6	=--REPLACE(LEFT(C1,4),1,0,RIGHT(C1,2))

 

[Micron]

Thanks. Next task is to figure how the double minus signs (which coerce true/false to 1/0?) applies here. I'm imagining it has the same effect as adding 0 to a string. Interesting that in Access, 0 is false, but -1 is True.