Custom date formatting

[Del Cotter]

Is there a way to format an Excel date such that

- the day of the week appears as a single letter? SMTWTFS

or

- the month appears as a single letter? JFMAMJJASOND

without resorting to text expressions?

 

[Ron Rosenfeld]

Is there a way to format an Excel date such that

- the day of the week appears as a single letter? SMTWTFS

or

- the month appears as a single letter? JFMAMJJASOND

without resorting to text expressions?

No
--ron

 

[Bernard Liengme]

It cannot be done with just formatting but these formulas may help
=LEFT(TEXT(E1,"ddd"))
=CHOOSE(WEEKDAY(A1),"S","M","T","W","T","F","S")
=LEFT(TEXT(A1,"mmm"))
=CHOOSE(MONTH(A1),"J","F","M","A","M","J","J","A","S","O","N","D")
best wishes

 

[Peo Sjoblom]

Another way


for months

LEFT(TEXT(A1,"mmm"))

for days


LEFT(TEXT(A1,"ddd"))

 

[Del Cotter]

No

Short and sweet. That was all I needed to know, thanks.

 

[Del Cotter]

Another way
for days

LEFT(TEXT(A1,"ddd"))

Thanks guys. I can do it as text, but I had hoped for a formatting
option that would let me retain the date nature of the cell contents.

 

[Rick Rothstein \(MVP - VB\)]

Is there a way to format an Excel date such that

- the day of the week appears as a single letter? SMTWTFS

or

- the month appears as a single letter? JFMAMJJASOND

Wouldn't doing that make it harder to decipher which month or day is
represented by your date and, for some months in some years, impossible?

For example, (assuming a display format) what date will this be?

T, J 8, 2008

Rick

 

[Del Cotter]

Wouldn't doing that make it harder to decipher which month or day is
represented by your date and, for some months in some years, impossible?

For the purpose I have in mind, it's not a problem, because the dates
will never occur at random, but always in context. So Tuesday is always
distinguishable by being a T flanked by an M and a W, while Thursday is
always a T flanked by a W and an F.

Similarly January is that J that is followed by an F, June is the J
followed by a J, and July is the J that is followed by an A.

I can do it with text functions okay, but I had hoped for a date format.
I suspect the ambiguity you describe is the exact reason the Excel
programmers did not make it available as an option.

 

[Ron Rosenfeld]

Short and sweet. That was all I needed to know, thanks.

You're welcome.

Such a response was made possible by the fact that you stated your question
completely!
--ron

 

[Del Cotter]

"Rick Rothstein (MVP - VB)" said:

For the purpose I have in mind, it's not a problem, because the dates
will never occur at random, but always in context. So Tuesday is
always distinguishable by being a T flanked by an M and a W, while
Thursday is always a T flanked by a W and an F.

Updating long after my original query to say that sometimes the Excel
experts get it wrong: there is indeed a date option that displays the
month as a single letter, even though that risks ambiguity. It's right
there in the "Date" number formats, at least in Excel 97. The format
"mmmmm" will display January (or June, or July) as "J", and so on.

Sadly, there isn't an equivalent for days of the week. "ddddd" just
defaults to "dddd" and spells out the whole day.

 

[Shane Devenshire]

Hi,

=LEFT(TEXT(A1,"DDD"))

If you have a date in cell A1 then this formula will return a single letter
abbreviation of the day of the week. Of course the problem here is that
you must put the formula in another cell.

Cheers,
Shane Devenshire

 

[Del Cotter]

=LEFT(TEXT(A1,"DDD"))

If you have a date in cell A1 then this formula will return a single
letter abbreviation of the day of the week. Of course the problem
here is that you must put the formula in another cell.

Shane, I'm familiar with text formulae. The original query was about not
using them.