Formatting Question #3

I need some help with the following: �Excel Version 2002�  I hav
inserted a Calendar Control into my spread sheet and linked it to 
cell. My problem is that I want the date selected within the calenda
to return a date formatted as Monday, October 31, 2004. The linked cel
is formatted correctly, however when selecting dates within th
calendar, it will only return dates formatted as 10-31-2004. Thanks i
advance for any help with this matter

--
Db171
-----------------------------------------------------------------------
Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591
View this thread: http://www.excelforum.com/showthread.php?threadid=27408

0
11/1/2004 5:21:57 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
439 Views

Similar Articles

[PageSpeed] 33

The problem is that the calendar control does not return a true date value, 
it just returns a string that looks like a date.

You can do a couple things as a workaround - one is to move the linked cell 
somewhere out of sight and put this formula in its place: =DATEVALUE(address 
of LinkedCell).  With the right date format you should get what you want.

Another approach is to use a macro to return a true date to a cell.  First 
get rid of the linked cell |(in Properties) since you don't need it any 
more.  Then go to the sheet's code module (right click on the sheet tab and 
pick "view code") and paste in this code and edit the cell address as 
necessary:

Private Sub Calendar1_Click()
    Range("A1").Value = Calendar1.Value
End Sub

Give this cell (A1 in my example) the desired date format.

-- 
Jim Rech
Excel MVP
"Db1712" <Db1712.1f11nx@excelforum-nospam.com> wrote in message 
news:Db1712.1f11nx@excelforum-nospam.com...
|
| I need some help with the following: �Excel Version 2002�  I have
| inserted a Calendar Control into my spread sheet and linked it to a
| cell. My problem is that I want the date selected within the calendar
| to return a date formatted as Monday, October 31, 2004. The linked cell
| is formatted correctly, however when selecting dates within the
| calendar, it will only return dates formatted as 10-31-2004. Thanks in
| advance for any help with this matter.
|
|
| -- 
| Db1712
| ------------------------------------------------------------------------
| Db1712's Profile: 
http://www.excelforum.com/member.php?action=getinfo&userid=15912
| View this thread: http://www.excelforum.com/showthread.php?threadid=274086
| 


0
jrrech (1932)
11/1/2004 2:13:47 PM
Reply:

Similar Artilces:

CRM 3.0 and SBS 2003 SP1 Hell solved... a bit.
Well I have spent 3 days and it's 3 am now.. but I have a level of success. PROBLEM Basically CRM 3.0 was failing the install step at the SQL reporting service pre install check. WHAT I TRIED that FAILED Well almost everyting, like 25 rollbacks vitrual server setups etc. A. 1. Installing SBS basic 2. Patched to SP 3. Install and patch SQL 2000 4. Manual install of reporting service 5. Patched Reporting service CRM Failed Preinstall check B. 1. SBS Basic 2. Patched SBS to SP1 3. Install SQL 2005 including Reproting service CRM Pre ins...

Number Format and percent
Dear all, 1. I am new to access program. I need to have 0.67 in a column in a table but when I put this into the column then it returns me 1. Is there a way to have it 0.67? 2. I need to have percent format in one of my columns, but instead of 5% it shows 500%, I want to have it as 5%. I will be thankful to receive feedbacks on the above issues. Thanks, Daoud 1. If your field is long integer it won't work. I use decimal 2. Don't multiply by 100 (the percent format does it for you) -- http://www.ready4mainstream.ny911truth.org/index.html "Daoud Fakhry" wrote: &...

Excel Number format in Indian style (Lacs, Crores)
HI, Someone please help me in changing numbers in excel to Indian forma .. say for ex.... 123456789 has to be displayed as 12,34,56,789 whic basically is Rs. 12 crores 34 lacs 56 thousand and 789 rupees as pe Indian currency system.. thanks amit chan -- Message posted from http://www.ExcelForum.com From a Google search here is a reply by David Richie to the same question in January If you have Excel 2002 or later look up bhattext in help. Otherwise the following format might help you [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00 (This format separates g...

Activating cell format
I have a number of cells that have numbers in them but the cell formats have been set to 'text'. Therefore any SUM formulas relating to these cells will not add up the values as the cells are only recognised as text. okay, I know how to change the cells back to 'general' or 'number' and I have done this. The problem is my formula will still not add up these cells unless I physically go back into each individual cell with a number to 're-activate' it by pressing F2 and enter (after which the formula calculates). Is there a quicker way of 're-activa...

Question on Installing SP1
A friend called and she has a new iMac and has Office 2008 installed. She was concerned about upgrading and I advised he to download Microsoft Office 2008 for Mac Service Pack 1 (12.1.0) <http://www.microsoft.com/downloads/details.aspx?FamilyId=395D1487-A3A6-4 106-A0F8-4D6E1D6D89D2&displaylang=en> Now I am concerned that she has not installed the 12.0.1 update. My question is .... Does the Microsoft Office 2008 for Mac Service Pack 1 (12.1.0) contain the 12.0.1 updates or do they need to be installed before SP1? "aRKay" wrote: > Now I am concerned that she has...

Online defragmentation questions
Our Exchange 5.5 database stopped last week due to a mailbox with no limit filling the server to capacity. I instructed users to delete messages and empty their folders, now I'm interested in tracking space usage. The online defragmentation will report this, but I cannot figure out what triggers this to run, or how I can force it to run at a specific time so that I can get see the results when I want them, not when Exchange wants to give them to me. My questions are: What Triggers the OLD(online defragmentation) of the ISPRIV? How can I force this to run immediately? Please help...

Specs of formats
Hi folks! Using these formats: [h]:mm [hh]:mm Does anyone know the exact limits on hours before each will fail? I'm thinking that the limit for [hh]:mm is 9999 hrs, but I'm not sure. And for [h]:mm, I don't know this one. Thanks! Biff IIRC, you can't directly enter a number in either format greater than 9999:59:59 But you can use the format for much bigger numbers. Experimentally, I get the formats turning to ####s when the value is greater than 2957003.99999 which corresponds to 70968095:59 In article <29ad01c5040d$efb66080$a501280a@phx.gbl&g...

Global Address List #3
We have two Windows 2000 AD domains, each with their own Exchange 2000 server. We would like to be able to share the Global Address Lists between the two domains. What is the easiest way to do this Thanks! ...

Conditional Format ... Compare Columns?
Excel 2000 ... Col B contains 1000 alpha-numerics (many repeat) Col D contains 50 alpha-numerics (none repeat) I would like "Conditional Formatting" Formula in Col B to high-lite all alpha-numerics not found in Col D. Thanks ... Kha Select column B and add this formula in CF =COUNTIF(D:D,B1)=0 -- HTH RP (remove nothere from the email address if mailing direct) "Ken" <anonymous@discussions.microsoft.com> wrote in message news:0c7e01c52fb7$e49c3410$a601280a@phx.gbl... > Excel 2000 ... > > Col B contains 1000 alpha-numerics (many repeat) > >...

Disabling Styles and Formatting
Is there a way using VBA to create a macro that will disable the Styles an Formating toolbar and button to access it. This macro will do it: Sub DisableFormattingToolbar() CommandBars("Formatting").Enabled = False End Sub You may have to set it to happen when the workbook opens, if you want this to happen when a user opens the book. tj "Nick Miller" wrote: > Is there a way using VBA to create a macro that will disable the Styles an > Formating toolbar and button to access it. ...

Icon Sets Conditional Formatting in Excel 2007
So, I want to be able to use the Icon Sets in conditional formatting with text values. I bet there are a bunch of other people out there that would like to have the text "green", "yellow" or "red" converted to the appropriate stoplight icon, but I cannot figure out how to make this work. Is it possible? Excel 2007 Icons Maybe not quite what you wanted... http://www.mediafire.com/file/dytjrggnxbm/09_26_09.xlsx On Sep 26, 9:27=A0am, Herbert Seidenberg <herbds7-ms...@yahoo.com> wrote: > Excel 2007 > Icons > Maybe not quite what you wanted...http://ww...

Time format #3
I have selected 1:30 PM as the format for time. I thought I could just type in the numbers without the colon and the time would be entered correctly, but it enters 12:00 AM every time. Is there any way to enter the time without having to enter the colon? one way have a look at this example of a macro solution http://www.mrexcel.com/articles/enter-time-without-colon.php or I found it easier to create an autocorrect entry I used "--" to be changed into ":" That way you can enter the colon by using the number pad only. -- Greetings from New Zealand "vangerm&qu...

Send/receive messages #3
I just started Outlook 2003 on my desktop. I also have Outlook 2003 on my laptop and on the laptop it works just fine. The problem I have with outlook on my desktop is that when I try to send and receive messages regarding my gmail account it does not download any of my new messages. My messages are sent but my messages in my inbox are not dowloaded. Can anyopne please help me? It works great but what is the use if if I can not see my inbox... no problems are reported, there just are no messages in outlook after it has sent/received. Regards C C <C@discussions.microsoft.com> w...

Disk Defragmenter Question
Is it normal for Disk Defragmenter to Analyze the disk AFTER it defragments? I know it does it before and then I click 'Defrag Now' and it proceeds to defrag my hard drive. But today I was watching it and noticed that all of a sudden it stopped defragmenting and the info changed to 'Analyzing disk...'. Thanks, James its when the defrag fail.. rerun the defrag.. -- ashvin :D Just chill -pls post your feedback on my reputation, would be of great help to fine tune and provide better support..- That's what I thought. James "ashvin&qu...

Dates in Excel
I have a column of dates, they are in yyyymmdd format (no slashes or anything) and they're text. I cannot format them, how do I do this? Basically, I want to change 19700429 to 4/29/1970, but for a whole column of different dates (dates not in sequential order). Thanks in advance!!!!! Insert a helper column. Then, assuming your "19700429" is in cell A1, paste the following formula into B1. =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Copy the formula down and you'll be all set. Then if you want to get rid of the helper column, copy the values in column B, and paste ...

... Can I Copy Conditional Formatting...
I have set-up Conditional Formatting on Worksheet # 1, and would like to copy that Conditional Formatting to all my Worksheets. Can I copy the Conditional Formatting I have on Worksheet # 1 to any or all of my additional Worksheets? Darrell "Dr. Darrell" <DrDarrell@discussions.microsoft.com> wrote in message news:7853A626-37FF-404A-8713-E9047AE6AC47@microsoft.com... >I have set-up Conditional Formatting on Worksheet # 1, and would like to >copy > that Conditional Formatting to all my Worksheets. > > Can I copy the Conditional Formatting I have on Workshe...

Graphing Question #4
Greetings! I am trying to graph a set of data in 'XY (Scatter)' and am running into a problem I've never seen before. There are only seven values for 'X' - numbers 0 through 6. 'Y' is a set of percentages ranging from '0.00%' to '13.00%' I have about 7,000 of these pairs. When I graph this, I am not of seeing the six discreet bands of percentages that I would expect. For some reason, instead of graphing the 'X' value, it is graphing the order that the 'Y' value comes in the list. Instead of graphing: RATING INCREASE 4 ...

Cannot send email #3
From: Crystal Harriman <timandcrys1@msn.com> Subject: Can receive just can't send email Error No. 0x800ccc0e Date: Tuesday, January 27, 2004 12:45 PM Outlook will not send my email. I am using Win 98 with Outlook 2000. I've checked with my ISP to see that the pop and smtp info is correct and it is. Keep getting the message Unable to connect to the server. Error Number 0x800ccc0e. Any ideas? Check with your ISP if your outgoing server requires authentication andn if so if the settings for this are the same as your user and password. Also check with your ISP if the USE...

CRM 3.0 Courseware
Hi, Where can I purchased the course material for "Customization in MS CRM 3.0" The ecourse is too much for my blood so I rather just get the course material. Thanks. JN I would call into MBS. You should be able to buy the courseware now. Also not the courseare titles should help you pass the exams so they are good value ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "JN" <jnsimex@rogers.com> wrote in message news:%23Vc2SOwRGHA.4264@TK2MSFTNGP11.phx.gbl... > Hi, > > Where can I purchased the ...

Comma Style format, default change
I'd like to change my comma for numbers: 1,000.00 to be 1,000 I can change the style in the toolbar icon (modify), but that only works for the current file. when i open another spreadsheet the toolbar icon puts to two zeros back into the right of the decimal point. HOW CAN I PERM. CHANGE THE COMMA STYLE ICON? DAVE You can change it for any new workbooks that are based on a template. Create new workbook with that style. Then save it in your XLStart folder (location varies with versions of windows & excel). Now each workbook you create new will use that as the default. You co...

Configuration questions
Got some basic configuration questions... I'm using a newer version of Visio in the client's office than I have on my own machine so there've been some changes since the last time I wandered into some differences that are annoying me. Any help you can give me would be appreciated. Thanks. Where is the setting to prevent Visio from displaying the Template Help pane whenever I start a new drawing? I learned how to create a basic diagram ages ago so I really don't need that help and it's annoying to have to close it every time. I'd also like to either alter the default ...

Excel
I am trying to get my head around the following scenario � bu struggling. Please can someone help: I have setup 2 data validation lists: �Consequence� List 1 : Serious, Significant, Minor �Likelihood� List 2 : Probable, Possible, Remote, Nil How do I setup 2 further lists which act on that initial preference I.e. so if Serious & Probable were chosen, 2 further set lists woul appear. (The lists would change depending on the initial preference) (Hope you follow) I then need to equate the data into a numerical form. I have studied the contexture site in some detail but still stuck! T...

Cmd Line Directory Export question 5.5
I'm trying to export just certain data from my 5.5 server by using the cmd line export. I made a header file (csv) with just the headers that I need. When I perform the cmd line export I do not get the data that I have specified in the header file. The export contains all information from Exchange. What am I doing wrong? are you specifying the header file in the export.ini ? On Thu, 3 Jun 2004 13:16:03 -0700, "jenlee64" <anonymous@discussions.microsoft.com> wrote: >I'm trying to export just certain data from my 5.5 server by using the cmd line export. I made a...

Questions re Linksys Extender
I have a Linksys DMA2100, which I=92ve set up to drive a TV through a wireless connection to my Windows 7 computer. I'm running Windows Media Center exclusively to play WMP ripped CD tracks on my computer. Now here come two probably hopelessly na=EFve questions. 1. Can I use the Extender to drive a set of external speakers, instead of the TV? The TV is in a far-off room, and the speakers would be much closer. I'm hoping I could just connect the speakers to the Audio outputs of the Extender, and then select the tracks to play from my computer, since the Extender=92s remote ...

CRM Server 3.0 Fails
We are installing CRM server 3.0 under SBS 2003. The setup terminates with the following "the Microsoft search service cannot be administered under the present account" changing the services logon account from the "local system' account to the administrators account made no difference. All replies are valued very much. thanks, Steven ...