Concatenate formula

[Jmoz092]

I'm having trouble getting a mid() formula to work inside of a concatenate formula.

I have a date in column A (mm/dd/yy) and I want to appear in column O as mm-dd-yy.

In column O I have the following formula, but I'm not getting the desired outcome.

[COLOR=#000000][FONT=&quot]=CONCATENATE(MONTH[COLOR=#006107]([/COLOR][COLOR=#0057d6]A32[/COLOR][COLOR=#006107])[/COLOR], "-", DAY[COLOR=#006107]([/COLOR][COLOR=#0057d6]A32[/COLOR][COLOR=#006107])[/COLOR], "-", MID[COLOR=#006107]([/COLOR][COLOR=#0057d6]A32[/COLOR],4,2[COLOR=#006107])[/COLOR])[/FONT][/COLOR]

So, the date in A32 is "1/31/18" and in O32 I'd like "01-31-18" to appear. With the aforementioned formula, I'm getting "1-31-31" in column O.

Any suggestions?

 

[Marcílio_Lobão]

Jmoz092, Good evening.

Try to use:

example: O32 --> =A32

Format O32 as date --> Custom --> mm-dd-yy

is that what you want?
I hope it helps.

 

[cooper645]

I have used your formula and amended it as follows:
=CONCATENATE(MONTH(A32), "-", DAY(A32), "-", RIGHT(YEAR(A32),2))

However you can't then use it as a date for calculations.


If this is not a problem then there is a more elegant formula:
=TEXT(A32,"mm-dd-yy")


Or alternatively could you not simply use:
=A32

and then change that cell/column format to a custom format as follows:
mm-dd-yy


All of these solutions give your desired result, but the last one allows you to continue to use the date in further calculations.


HTH,
Coops

 

[cooper645]

Beaten to it

Jmoz092, Good evening.

Try to use:

example: O32 --> =A32

Format O32 as date --> Custom --> mm-dd-yy

is that what you want?
I hope it helps.

 

[Jmoz092]

No, that is not working. Maybe I'm looking in the wrong place, but I do not see and option to format the cell as a date with hyphens in that exact manner

 

[cooper645]

Format the cell, select number tab, select custom and enter the format mm-dd-yy

 

[Jmoz092]

I have used your formula and amended it as follows:
=CONCATENATE(MONTH(A32), "-", DAY(A32), "-", RIGHT(YEAR(A32),2))

However you can't then use it as a date for calculations.


If this is not a problem then there is a more elegant formula:
=TEXT(A32,"mm-dd-yy")


Or alternatively could you not simply use:
=A32

and then change that cell/column format to a custom format as follows:
mm-dd-yy


All of these solutions give your desired result, but the last one allows you to continue to use the date in further calculations.


HTH,
Coops

first: works as I intend with :

[COLOR=#000000][FONT=&quot]=CONCATENATE("0", MONTH[COLOR=#006107]([/COLOR][COLOR=#0057d6]A32[/COLOR][COLOR=#006107])[/COLOR], "-", DAY[COLOR=#006107]([/COLOR][COLOR=#0057d6]A32[/COLOR][COLOR=#006107])[/COLOR], "-", RIGHT[COLOR=#006107]([/COLOR]YEAR[COLOR=#ab30d6]([/COLOR][COLOR=#0057d6]A32[/COLOR][COLOR=#ab30d6])[/COLOR],2[COLOR=#006107])[/COLOR])[/FONT][/COLOR]

second: works as I intend

third: doesn't work. It displays with "/"

fourth: I can not locate a custom format that displays "-"

 

[Jmoz092]

Format the cell, select number tab, select custom and enter the format mm-dd-yy

I.did.not.know that
thank you

 

[cooper645]

Welcome