Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
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? -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Mon, 11 Jun 2007 17:58:41 +0100, Del Cotter wrote:
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Del Cotter" wrote in message ... 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? -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
Another way
for months LEFT(TEXT(A1,"mmm")) for days LEFT(TEXT(A1,"ddd")) -- Regards, Peo Sjoblom "Del Cotter" wrote in message ... 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? -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions,
Ron Rosenfeld said: Del Cotter wrote: Is there a way to format an Excel date such that the day of the week appears as a single letter without resorting to text expressions? No Short and sweet. That was all I needed to know, thanks. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions,
Peo Sjoblom said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions,
"Rick Rothstein (MVP - VB)" said: Is there a way to format an Excel date such that 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 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Mon, 11 Jun 2007 19:09:44 +0100, Del Cotter wrote:
On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions, Ron Rosenfeld said: Del Cotter wrote: Is there a way to format an Excel date such that the day of the week appears as a single letter without resorting to text expressions? No 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions,
I said: "Rick Rothstein (MVP - VB)" said: Is there a way to format an Excel date such that 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 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
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" wrote in message ... On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions, I said: "Rick Rothstein (MVP - VB)" said: Is there a way to format an Excel date such that 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 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom date formatting
On Sat, 23 Aug 2008, in microsoft.public.excel.worksheet.functions,
Shane Devenshire said: =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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
Custom Formatting | Excel Worksheet Functions | |||
custom formatting?? | Excel Worksheet Functions | |||
How can I get yesterdays date in a custom footer &[DATE]-1 does . | Charts and Charting in Excel | |||
custom formatting | Setting up and Configuration of Excel |