Custom Format-- date question

[araymc]

I need to take

9/10/1995

and custom format to appear as

S101995

I can't figure out how to on my custom formatting.

Anyone?

Thanks...
Anita

 

[AlphaFrog]

In your example date 09/10/1995, if 10 is the month...
"S"mmyyyy

Or if 10 is the day...
"S"ddyyyy

 

[silkfire]

Does S denote September?

 

[CMDMA03]

Here is a formula that will get you what you want:

=LEFT(TEXT(A8,"MMM"),1)&TEXT(A8,"DD")&TEXT(A8,"YYYY")

 

[Scott Huish]

Here's another formula:

=LEFT(TEXT(A1,"mmm"))&TEXT(A1,"ddyyyy")

 

[araymc]

Thanks to all who have responded so quickly.

Yes the 9 is equivalent to September.

I know I will have more questions as my work role has changed somewhat and I love to take what little I do know about Excel and push it to streamline the work to go FASTER! LOL It's a wonderful little secret to make your workday go smoother.

Anita

 

[araymc]

Well, dang it! Now I have been thrown a curve ball. If the day is only 1 digit I don't need a zero to show up.

Original data
9/01/2011

after formula as is
S012011

needs to be
S12011

Sorry to be a pain! I should have read the email from co-worker more thoroughly.

Thanks!
Anita

 

[Scott Huish]

=LEFT(TEXT(A1,"mmm"))&TEXT(A1,"dyyyy")

 

[araymc]

Thank you Hot Pepper!

 

[Rick Rothstein]

Here is a solution that does not use concatenation (one less function call as well)...

=REPLACE(TEXT(A1,"mmmdyyyy"),2,2,"")