Line break in custom date format?

I want to display the date like this: Ddddd, MM/DD/YY

For example 'Monday, 12/08/2009'.  

However, I want a line break after the comma so that the spelled-out day and 
date are on different lines in the same cell.

It's harder than it sounds.  In the cell, the value is '12/08/2009'.  But 
the format makes it display as Monday, 12/08/2009.  I can't just put an 
alt+enter in the middle of the date value, as that value would no longer make 
sense.

Is there a code I can put in a custom format that will allow me to insert a 
line break?

Thanks, Much.
0
Utf
12/8/2009 7:02:03 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
2568 Views

Similar Articles

[PageSpeed] 54

You can try this but I don't like how it turns out.

Custom format:

dddd,ALT 010m/d/yyyy

ALT 010 means hold down the ALT key then *using the numeric keypad*, type 
010. When you release the ALT key a little square will appear.

Then format to wrap text.

Adjust the row height/column width as needed.

The problem is the column width is set as if the date was all on a single 
line.

The column width will be as wide this:

Monday, 12/08/2009

Not this:

Monday,
12/08/2009

-- 
Biff
Microsoft Excel MVP


"Ivor Big Botty" <Ivor Big Botty@discussions.microsoft.com> wrote in message 
news:A5056D10-82EC-4570-A3B1-6DBA3FFD7E13@microsoft.com...
>I want to display the date like this: Ddddd, MM/DD/YY
>
> For example 'Monday, 12/08/2009'.
>
> However, I want a line break after the comma so that the spelled-out day 
> and
> date are on different lines in the same cell.
>
> It's harder than it sounds.  In the cell, the value is '12/08/2009'.  But
> the format makes it display as Monday, 12/08/2009.  I can't just put an
> alt+enter in the middle of the date value, as that value would no longer 
> make
> sense.
>
> Is there a code I can put in a custom format that will allow me to insert 
> a
> line break?
>
> Thanks, Much. 


0
T
12/8/2009 7:25:23 PM
Format the cell(s) to Wrap Text, adjust the row height to accommodate
two rows, and widen the column to suit. Then in Custom format enter:

dddd,<CTRL-J>mm/dd/yy

You only need 4 d's to specify the full day. <CTRL-J> gives you the
line break that you are after, although it is difficult to see the
full format string in the format cells panel.

Hope this helps.

Pete

On Dec 8, 7:02=A0pm, Ivor Big Botty <Ivor Big
Bo...@discussions.microsoft.com> wrote:
> I want to display the date like this: Ddddd, MM/DD/YY
>
> For example 'Monday, 12/08/2009'. =A0
>
> However, I want a line break after the comma so that the spelled-out day =
and
> date are on different lines in the same cell.
>
> It's harder than it sounds. =A0In the cell, the value is '12/08/2009'. =
=A0But
> the format makes it display as Monday, 12/08/2009. =A0I can't just put an
> alt+enter in the middle of the date value, as that value would no longer =
make
> sense.
>
> Is there a code I can put in a custom format that will allow me to insert=
 a
> line break?
>
> Thanks, Much.

0
Pete_UK
12/8/2009 7:29:42 PM
As I live in a different zone and use dd/mm/yyyy for fates - my suggestion 
might have to be fine-tuned by you.
The general principal is:
=TEXT(WEEKDAY(TODAY()),"dddd")&", "&TEXT(TODAY(),"mm/dd/yyyy")
Micky


"Ivor Big Botty" wrote:

> I want to display the date like this: Ddddd, MM/DD/YY
> 
> For example 'Monday, 12/08/2009'.  
> 
> However, I want a line break after the comma so that the spelled-out day and 
> date are on different lines in the same cell.
> 
> It's harder than it sounds.  In the cell, the value is '12/08/2009'.  But 
> the format makes it display as Monday, 12/08/2009.  I can't just put an 
> alt+enter in the middle of the date value, as that value would no longer make 
> sense.
> 
> Is there a code I can put in a custom format that will allow me to insert a 
> line break?
> 
> Thanks, Much.
0
Utf
12/8/2009 8:11:01 PM
Sorry for the missunderstanding.
This one may be the requested formula:
=TEXT(WEEKDAY(TODAY()),"dddd")&", "&CHAR(10)&TEXT(TODAY(),"mm/dd/yyyy")
Micky


"Ivor Big Botty" wrote:

> I want to display the date like this: Ddddd, MM/DD/YY
> 
> For example 'Monday, 12/08/2009'.  
> 
> However, I want a line break after the comma so that the spelled-out day and 
> date are on different lines in the same cell.
> 
> It's harder than it sounds.  In the cell, the value is '12/08/2009'.  But 
> the format makes it display as Monday, 12/08/2009.  I can't just put an 
> alt+enter in the middle of the date value, as that value would no longer make 
> sense.
> 
> Is there a code I can put in a custom format that will allow me to insert a 
> line break?
> 
> Thanks, Much.
0
Utf
12/8/2009 8:17:01 PM
Add another step to this routine and it becomes a little less bad.

Apply number format that Biff/Pete suggested.
But then go to the Alignment tab
Uncheck Wrap text (if it's checked)
Check shrink to fit
check wrap text

The columnwidth can be narrower (but still doesn't autofit).  Setting the
rowheight is also manual.



"T. Valko" wrote:
> 
> You can try this but I don't like how it turns out.
> 
> Custom format:
> 
> dddd,ALT 010m/d/yyyy
> 
> ALT 010 means hold down the ALT key then *using the numeric keypad*, type
> 010. When you release the ALT key a little square will appear.
> 
> Then format to wrap text.
> 
> Adjust the row height/column width as needed.
> 
> The problem is the column width is set as if the date was all on a single
> line.
> 
> The column width will be as wide this:
> 
> Monday, 12/08/2009
> 
> Not this:
> 
> Monday,
> 12/08/2009
> 
> --
> Biff
> Microsoft Excel MVP
> 
> "Ivor Big Botty" <Ivor Big Botty@discussions.microsoft.com> wrote in message
> news:A5056D10-82EC-4570-A3B1-6DBA3FFD7E13@microsoft.com...
> >I want to display the date like this: Ddddd, MM/DD/YY
> >
> > For example 'Monday, 12/08/2009'.
> >
> > However, I want a line break after the comma so that the spelled-out day
> > and
> > date are on different lines in the same cell.
> >
> > It's harder than it sounds.  In the cell, the value is '12/08/2009'.  But
> > the format makes it display as Monday, 12/08/2009.  I can't just put an
> > alt+enter in the middle of the date value, as that value would no longer
> > make
> > sense.
> >
> > Is there a code I can put in a custom format that will allow me to insert
> > a
> > line break?
> >
> > Thanks, Much.

-- 

Dave Peterson
0
Dave
12/8/2009 8:23:31 PM
That's much better.

-- 
Biff
Microsoft Excel MVP


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B1EB5C3.7684F9EF@verizonXSPAM.net...
> Add another step to this routine and it becomes a little less bad.
>
> Apply number format that Biff/Pete suggested.
> But then go to the Alignment tab
> Uncheck Wrap text (if it's checked)
> Check shrink to fit
> check wrap text
>
> The columnwidth can be narrower (but still doesn't autofit).  Setting the
> rowheight is also manual.
>
>
>
> "T. Valko" wrote:
>>
>> You can try this but I don't like how it turns out.
>>
>> Custom format:
>>
>> dddd,ALT 010m/d/yyyy
>>
>> ALT 010 means hold down the ALT key then *using the numeric keypad*, type
>> 010. When you release the ALT key a little square will appear.
>>
>> Then format to wrap text.
>>
>> Adjust the row height/column width as needed.
>>
>> The problem is the column width is set as if the date was all on a single
>> line.
>>
>> The column width will be as wide this:
>>
>> Monday, 12/08/2009
>>
>> Not this:
>>
>> Monday,
>> 12/08/2009
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>> "Ivor Big Botty" <Ivor Big Botty@discussions.microsoft.com> wrote in 
>> message
>> news:A5056D10-82EC-4570-A3B1-6DBA3FFD7E13@microsoft.com...
>> >I want to display the date like this: Ddddd, MM/DD/YY
>> >
>> > For example 'Monday, 12/08/2009'.
>> >
>> > However, I want a line break after the comma so that the spelled-out 
>> > day
>> > and
>> > date are on different lines in the same cell.
>> >
>> > It's harder than it sounds.  In the cell, the value is '12/08/2009'. 
>> > But
>> > the format makes it display as Monday, 12/08/2009.  I can't just put an
>> > alt+enter in the middle of the date value, as that value would no 
>> > longer
>> > make
>> > sense.
>> >
>> > Is there a code I can put in a custom format that will allow me to 
>> > insert
>> > a
>> > line break?
>> >
>> > Thanks, Much.
>
> -- 
>
> Dave Peterson 


0
T
12/8/2009 9:33:56 PM
Reply:

Similar Artilces:

Customize Outlook today does not work #5
I have read the replies about if MS update 813489 is install customize outlook today does not work. What if you do NOT have MS update 813489 install and it still does not work then what? Here is a good one for you. If you update to Office 2003 then it works. But if you install (not update) Office 2003 you can open customize outlook today, but you can not make or save any changes. Error "an activeX control on this page might be unsafe to.... I am having the same problem. Only update 813489 has not been loaded on my computer, because it is not relevant to my system. However, I did ...

Failed to customize the dialog box of toolbar
I cannot fill the left-hand list box of toolbar's dialog box,and if I delete one button in toolbar,I lost it. Below is my code: [code] #include <windows.h> #include <commctrl.h> #include "resource.h" #define ID_TOOLBAR 20001 static char *szAppName = "Study Toolbar" ; LRESULT CALLBACK WndProc( HWND, UINT, WPARAM, LPARAM ) ; TBBUTTON tbButtons[] = { {STD_FILENEW, IDM_FILE_NEW, TBSTATE_ENABLED, TBSTYLE_BUTTON | BTNS_SHOWTEXT, 0, 0}, // 1 {STD_FILEOPEN, IDM_FILE_OPEN, TBSTATE_ENABLED, TBSTYLE_BUTTON, 0, 0}, // 2 {STD_FILESAVE, IDM_FILE_SAVE, TBSTATE...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

Time Zone / Incorrect Dates Displayed to User
When a user in the Central Time zone closes an opportunity and someone in the Mountain Time zone views it, the Actual Close Date is 1 day before the date the person in the Central Time zone selected. For instance... If someone in the Central Time zone closes an opportunity and selects the Actual Close Date as 6/2/2007: -- Users in the Central Time zone see: 6/2/2007 -- But users in the Mountain Time zone see: 6/1/2007 This is a big issue when running monthly reports as it makes some opportunities show in the wrong month... and will put that revenue in the wrong month. It would make mo...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

is there a way of using a second monitor as a customer display
This is a multi-part message in MIME format. ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Does anyone know if rms works with 2 monitors. What I mean can you use a second monitor as customer display or something similar. A lot of pos systems out there use a second monitor as customer display, I m not sure if rms supports that option or not but any help would be appreciated. thanks ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

Export Format not avaiable
"The Format in which you are attempting to output the currentobject is not available." I hate access sometimes. It just get's weird, throwing bogus error messages all over the place. I have about 30 seperate queries that I run out to spreadsheets via macro. I have already found out that things can get all screwed up, (meaning it bombs) when those spreadsheets already exist, so the first thing I do is delete the existing spreadsheets, then let them rip. I run into this every once in a while: 20 or so queries into my macro, a query will fail with the above er...

xy scatter format
Hi Hope someone can help. I have a chart i want to make look a little more professional. The chart plots three points within two boxes. the points should be inside the box box 1 has the following coordinates x y 15 70000 15 115000 21.5 155800 30 155800 30 115000 25 70000 15 70000 the chart scale is x 14-32 & y 60000-160000 I would like to make the plot area outside the box black to indicate the no go area. Is this possible? Then to take it further there should be a further smaller box (coordinates unimportant) inside the 1st box to indicate a warning. I would like the plot area ...

Date & Time file was last saved
Is there away to display in a cell the date & time the file was last saved? I know you can do this in word but is it possible in Excel 2002? Here is an UDF Function DocProps(prop As String) application.volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties(prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function Now you can use the following formula in one of your cells: =DocProps("Last save time") Obviously, the file has to have been saved. -- HTH Bob Phillips ... looking out across Poole Harbour to ...

Custom Report in CRM 3.0
Hi, I have a custom report built up using SSRS. My report project has two rdl files. First Report has some fields with links, on clicking any of the filed will show the details of the field in the second report. When I try to upload the report in CRM 3.0, I find only one RDL file can be uploaded. how do I upload both the files in crm?? Any help is greatly appreciated. Thanks Karthik You should able to upload both reports to CRM, when you try to upload the second report, what is the error? Darren Liu, Microsoft CRM MVP Crowe Horwath http://www.crowecrm.com On Jan 7, 10:28=A0am, K...

date macro
HI! Thanks in advance for helping. I would like to run a macro on a user form to take the date of report and see if the date is on or past the 26th and send the report to the its designated worksheet. looped. example: IF the report is anywhere created from 26th of the previous month to 25th of current month to go in to spreedsheet of that current month and continue on till project is finished. starting with the first day of contract to be awarded. the problem is i have to use thier form on excel. the thing is, the report will only have a "todays date" and the data to follow...

Caculating Columns Between Certain Dates
I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company?...

Post Exp Date in Master Rcd (row) from trans record
Hi, I'm using Access 2003 via Xp Office Pro. I'm working on a annual membership database. Each member has to renew each year if they want to stay a member. I want to keep a history of their membership. I have a membership table and will have a renewal table where I create a record / row each time a member renews. Key to both tables are automatic generated numbers. When I post the renewal transactions, I would like to change the expiration date that is on the master record. I realize that I would have the same data in both records, but for simplicity of the r...

Add working hours to date field
We offer computer support based on a certain response time in hours. I want to have a date field, on the case form, to automatically set x WORKING hours ahead, usually 8 hours. The actually hour value will be pulled from a contract. Does anyone know if this is possible or am I shooting way too high? ...

Extracting the month that a date refers to in another cell
In cell A1 I have the date 3/15/2003 (no formatting). In cell B1 I have the formula =year(A1). The year shows correctly as 2003. In cell C1 I have the formula =text(weekday(A1),"ddd"). The text shows as "Sat" which is correct. In cell D1 I have the formula =month(A1) which correctly shows '3'. I attempted to use =text(month(A1),"mmm") in cell D1, but it shows as "Jan". Can someone tell me what I am doing wrong? TIA, Alan =TEXT(A1,"mmm") for 3-letter month name and =TEXT(A1,"ddd") for 3-letter day name. where A1 houses a...

Multiple ship-to same customer
We have multiple ship-to accounts with single vendors, i.e. one of our customers has three offices that we drop ship supplies directly from the vendor. The vendors have set up specific account numbers for each singular office/location, i.e. any location we ship to has a unique customer number. We suspect if these individual account numbers are not indicated on our PO to the vendor since we fax many of our orders to the vendor (in addition to the ship-to address) that we will end up with orders shipped to the wrong office(s). How do we set up one vendor (to link all the items from tha...

condional formating count question
Hi, I have a row in which I have a condition that if two numbers are identical its formating the 2nd number in Red Font, Is there a way or formula that I can use to count these red font Numbers? Thanks in advance for you help, It is best to use the same means in a formula that you used to make the font RED in the first place rather than trying to use a function to check the color of a cell. You can take a look at Chip Pearson's page, Functions For Working With Cell Colors http://www.cpearson.com/excel/colors.htm for counting cells in a range with involving normal cell coloring for ...

X Axis needed for Line Chart #2
I have created two line images on a Line Chart using several data points. Now I would like to have an x axis that shows a marker for every 1/2 hour. Example: .5 1.0 1.5 2.0 2.5 3.0 and so on for up to 5.0 to represent hours. I created the data for these time points that coorespond to my data for line images but do not know how to show them in my x axis as markers. Any idea? My x axis shows nothing? Y axis is fine. I have data scale markers there. -- FL Does the chart know you have X data? Go to Chart menu > Source Data > Series tab. Is there anything in the ...

Pivot Table Customize functions in the Data Field
Hi all, I want to find out if it is possible to create custom formulas in the data field of a pivot table. I need to to show: Fleets of vehicles. How many of a particular fleet a particular company has on hand. A percentage of how much of that companies fleet is not operational. If no maintinane is completed between Now() and the end of the reporting period, What the % of maintenance failure we will have for a fleet. I think the best way to accomplish this is a pivot table but the built in functions are not going to handle the calculations I need. -- Thanks, Patrick PS, Don't ...

I get 2 message boxes
Hi I have the following code which runs when I open a second form... it works great but unfortunately I get a second system message box as well as my own custom box what have I missed out? can anyone help many thanks in advance : Private Sub Form_Open(Cancel As Integer) If Me.Recordset.RecordCount = 0 Then Cancel = True MsgBox "There are no records to display." End If End Sub What system message are you getting? What version of Access are you using? "Dic_nutana" <Dicnutana@discussions.microsoft.com> wrote in message news:5C862326-FCDF-45C8-BEFB-7333227C61B...

text to date..!!
if i am having a date in a cell as 30/11/2005 but formatted in text, what's the procedure to change the same to date format as "30-Mar-2005"? thks & regds! via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26725 View this thread: http://www.excelforum.com/showthread.php?threadid=495816 sorry! a small correction! if i am having a date in a cell as 30/11/2005 but formatted in text, what's the procedure to change the same to date format as &q...

debugger shows wrong line number
I am experiencing a rather strange behavior -- when I step through my program, the debugger shows the debugging cursor at the wrong line. Also, when I set a breakpoint at any line, the debugger stops at a different line (sometimes the next line or the one after). Any idea why I might be seeing this problem. I am using Visual Studio .NET 2003 >I am experiencing a rather strange behavior -- when I step through my >program, the debugger shows the debugging cursor at the wrong line. > >Also, when I set a breakpoint at any line, the debugger stops at a different >line (someti...