Convert the dd/mm portion in dd/mm/yyyy format to yyyymm format

[pacerfan07]

Hi,

I'm trying to convert a date that's typed as "11/12/2018" into "201211". So I want to drop the year, convert the 12 into 2012, move it to the front, and follow it with the 11.

Some of the dates have less digits. For example 7/15/2018 would need to become 201507.

Thanks!

 

[Joe4]

Is your typed entry entered as Text or Date?
If you are unsure, try this formula (for an entry in cell A1):
=ISNUMBER(A1)

If it returns TRUE, you have a valid date entry (dates are really numbers in Excel).
If it returns FALSE, you have a text entry.

If you have a valid date entry, you can simply change the cell format to "yyyymm" to get it to display the way you want.
Or, if you want it to appear in another cell as Text, you can use the formula:
=TEXT(A1,"yyyymm")

If it is a text entry, you can use the formula:
=TEXT(DATEVALUE(A1),"yyyymm")

 

[pacerfan07]

Thanks for the reply. The entry is a valid date entry. However, your formula converts 11/12/2018 into 201811. I need to somehow disregard the 2018 on the end. I should have phrased the question this way: I'm just concerned with the first couple digits 11/12. I want to then turn the 11/12 into 11/1/2012 if possible. Another example would be to turn 7/15/2018 into 7/1/2015.

 

[Joe4]

Another example would be to turn 7/15/2018 into 7/1/2015.

So, do you just want to get the first day of that particular month?
You can simply use the formula:

=DATE(YEAR(A1),MONTH(A1),1)

and then choose any date format you want.

 

[steve the fish]

The way im reading it you want one of these maybe:

=DAY(A1)*100+MONTH(A1)+200000
=DATE(20&DAY(A1),MONTH(A1),1)

 

[pacerfan07]

Thanks Steve that's what I was looking for.

 

[Joe4]

I was thrown off by your title.

Convert the dd/mm portion in dd/mm/yyyy format to yyyymm format

You really don't want "yyyy" in your format.
So, that "20" in your format just appears to be some static number, not representative of anything from the date itself.