 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / August 2008Custom 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?  SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. >Is there a way to format an Excel date such that > [quoted text clipped - 5 lines] > >without resorting to text expressions? No --ron >>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. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. >>>Is there a way to format an Excel date such that >>>the day of the week appears as a single letter [quoted text clipped - 3 lines] > >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 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 SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Is there a way to format an Excel date such that > [quoted text clipped - 5 lines] > > without resorting to text expressions? Another way for months LEFT(TEXT(A1,"mmm")) for days LEFT(TEXT(A1,"ddd")) SignatureRegards, Peo Sjoblom > Is there a way to format an Excel date such that > [quoted text clipped - 5 lines] > > without resorting to text expressions? >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. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. > Is there a way to format an Excel date such that > [quoted text clipped - 3 lines] > > - 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 >> 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. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. 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 [quoted text clipped - 7 lines] >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. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. 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 > On Mon, 11 Jun 2007, in microsoft.public.excel.worksheet.functions, > I said: [quoted text clipped - 19 lines] > Sadly, there isn't an equivalent for days of the week. "ddddd" just > defaults to "dddd" and spells out the whole day. >=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. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.