Disable auto (date) format?

In A3, I have a formula of the form =A1/A2/24, where A2 is time (date serial 
number) formatted h:mm:ss.  (A1 is a number formatted as General.)

When A3 is formatted as General, Excel changes the format to h:mm:ss every 
time I edit A3.  I have to change the format back to General manually, which 
is a nuisance.

I can avoid this by formatting A3 as Number.  But I would like it remain 
General.

Is there any option setting that disables this autoformat heuristic?

I don't mind if it turns off all "intelligent" autoformat selection.

I am using MS Office Excel 2003 SP3. 

0
Joe
4/7/2010 6:26:42 PM
excel.misc 78881 articles. 3 followers. Follow

3 Replies
2395 Views

Similar Articles

[PageSpeed] 40

Your logic sounds circular. By using General setting, you're saying that you 
want XL to use whatever it thinks is the natural format. But, then you say 
that you'd like a number to display (indiciating that you *do* care what the 
format is). But then you go back and say you want a General format??

If you *insist* on having the cell format remain General, you could 
accomplish this by losing precision with this formula (or something similar) 
and still have the cell format be General.
=VALUE(TEXT(A1/A2/24,"0.##"))

-- 
Best Regards,

Luke M
"Joe User" <joeu2004> wrote in message 
news:%23Ib2f$n1KHA.776@TK2MSFTNGP04.phx.gbl...
> In A3, I have a formula of the form =A1/A2/24, where A2 is time (date 
> serial number) formatted h:mm:ss.  (A1 is a number formatted as General.)
>
> When A3 is formatted as General, Excel changes the format to h:mm:ss every 
> time I edit A3.  I have to change the format back to General manually, 
> which is a nuisance.
>
> I can avoid this by formatting A3 as Number.  But I would like it remain 
> General.
>
> Is there any option setting that disables this autoformat heuristic?
>
> I don't mind if it turns off all "intelligent" autoformat selection.
>
> I am using MS Office Excel 2003 SP3. 


0
Luke
4/7/2010 7:13:09 PM
Sounds like what you want is to force Excel to ignore the date/time type 
in A2 and treat that value as the date serial number (that is, just a 
floating point number) instead.

Try using =A1/N(A2)/24 and see if that does what you want.

Look up the help on the N worksheet function.

Clif


"Joe User" <joeu2004> wrote in message 
news:%23Ib2f$n1KHA.776@TK2MSFTNGP04.phx.gbl...
> In A3, I have a formula of the form =A1/A2/24, where A2 is time (date 
> serial number) formatted h:mm:ss.  (A1 is a number formatted as 
> General.)
>
> When A3 is formatted as General, Excel changes the format to h:mm:ss 
> every time I edit A3.  I have to change the format back to General 
> manually, which is a nuisance.
>
> I can avoid this by formatting A3 as Number.  But I would like it 
> remain General.
>
> Is there any option setting that disables this autoformat heuristic?
>
> I don't mind if it turns off all "intelligent" autoformat selection.
>
> I am using MS Office Excel 2003 SP3.



0
Clif
4/7/2010 8:01:25 PM
"Luke M" <lukemoraga@nospam.com> wrote:
> Your logic sounds circular. By using General setting,
> you're saying that you want XL to use whatever it thinks
> is the natural format.

Yes, that makes sense.  I had not thought of it that way.  Thanks.


----- original message ------

"Luke M" <lukemoraga@nospam.com> wrote in message 
news:ejdidZo1KHA.260@TK2MSFTNGP05.phx.gbl...
> Your logic sounds circular. By using General setting, you're saying that 
> you want XL to use whatever it thinks is the natural format. But, then you 
> say that you'd like a number to display (indiciating that you *do* care 
> what the format is). But then you go back and say you want a General 
> format??
>
> If you *insist* on having the cell format remain General, you could 
> accomplish this by losing precision with this formula (or something 
> similar) and still have the cell format be General.
> =VALUE(TEXT(A1/A2/24,"0.##"))
>
> -- 
> Best Regards,
>
> Luke M
> "Joe User" <joeu2004> wrote in message 
> news:%23Ib2f$n1KHA.776@TK2MSFTNGP04.phx.gbl...
>> In A3, I have a formula of the form =A1/A2/24, where A2 is time (date 
>> serial number) formatted h:mm:ss.  (A1 is a number formatted as General.)
>>
>> When A3 is formatted as General, Excel changes the format to h:mm:ss 
>> every time I edit A3.  I have to change the format back to General 
>> manually, which is a nuisance.
>>
>> I can avoid this by formatting A3 as Number.  But I would like it remain 
>> General.
>>
>> Is there any option setting that disables this autoformat heuristic?
>>
>> I don't mind if it turns off all "intelligent" autoformat selection.
>>
>> I am using MS Office Excel 2003 SP3.
>
> 

0
Joe
4/7/2010 11:12:46 PM
Reply:

Similar Artilces:

Auto Accept Agent fails to complete install.
Has anyone run into this... using Exchange 2003 / Windows 2003 SP1? Error States roduct: Auto Accept Agent -- Error 1722. There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. Action CA_AutoAcceptInstaller.install, location: D:\Program Files\Exchsrvr\Agents\AutoAccept\Bin\AutoAcceptInstaller.exe, command: INSTALL 5 Thanks in advance, Are you sure you have all requisite permissions? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backup...

Auto-stop feature?
I would like to be able to play a DVD while I'm faling asleep and have Windows Media Player automatically stop at the end of the DVD (so the DVD drive doesn't run all night). I have the feature on my TV's DVD player, so I assume it is something that can also be done on my computer. I just can't figure out how. On Mon, 10 May 2010 19:00:01 -0700, hlagas <hlagas@discussions.microsoft.com> wrote: > >I would like to be able to play a DVD while I'm faling asleep and have >Windows Media Player automatically stop at the end of the DVD (so the DVD ...

Disable BU
A really quick one for you. If I disable a BU in MSCRM, does the corresponding OU in Active Directory become disabled? I think the answer probably 'No' as I actually cannot find a way to disable the OU manually and therefore cannot check, but if anyone has a definitive answer I would be grateful. Regards Peter John - thanks for your help. Regards Peter >-----Original Message----- >No, disabling BU in CRM will not disable corresponding OU in the AD, just like dissabling CRM user in CRM will not siable the corrsponding user in AD. > >"Pete" wrote: > &...

Print Preview "Prev Page" but is always disabled
Hello, I have created an MFC application and have added Printing and Print Preview capability. The only problem i have is that the Prev Page button on the Print Preview toolbar is always disabled. The "Two Pages" button and "Next Page" are enabled and work correctly. I am setting the CPrintInfo::SetMaxPage() and CPrintInfo::SetMinPage() and CPrintInfo::m_nCurPage values in OnPreparePrinting() but this doesnt seem to make a difference. Is their anything else that i can try which will enable this button? Thanks Andy ...

how can disable service with bat file
Hi, Please tell me, how can i disable service with bat file. What service? "Vijay" wrote: > Hi, > > Please tell me, how can i disable service with bat file. > Please tell me, how can i disable service with bat file. That's funny ... weren't we just talking about Permissions? :-) Anyway, the command to control a Windows Service (enable, start, stop, disable, etc) is SC.EXE. Run SC /? at a command prompt to see the syntax. Put the necessary SC command in a batch file, and you are all set. Hope it helps, Andrew -- amclar at ...

Parameter Query No Results
I have a parameter query works as long as there are related records in the tables. My problem is I when the query results are "null" or no records exist I want the query to automatically create a new record based on the parameter input. Is this possible? Example: [CustomerID] criteria shows Like "*" & [Enter Customer ID:] & "*" If this is a new record for this Customer my query returns no values. I want the query to automatically create a new record for this Customer or show all records related. Your help is GREATLY appreciated! ...

Conditional Formatting Parts Of Words
Hi all, I've got a number of cells labelled to express the status of a range o projects: either 'Red', 'Amber' or 'Green'. I've got conditiona formatting on these to turn the specific colour, and that's all fin and dandy. However, to reduce cell width, what I'd really like to do is to simpl have the first letter of each variable listing - i.e. 'R', 'A', 'G' Thus the 'ed' of 'Red' would go red, but the 'R' would stay black; th 'mber' of 'Amber' would go amber, etc. The colour coding is be...

Auto print HTML page from hyperlink in Excel?
I hope someone in this forum can help me with this question. I have just been sent an Excel 97 spreadsheet containing approx 900 rows of information about documents created through a web form. One column contains a full list of the URL information pointing to eac of these documents. Due to a legal requirement, every one of thes documents must be printed prior to actioning in an accounting syste (Yes, you read that right! Dontcha just love industrial relations?) To enable faster access for printing, I have created hyperlinks for al of these URL's in another column by using =HYPERLINK(M2) an...

How do i block or protect final date gantt?? 12-21-09
How do i protect or block this final date from Gantt???? -- PaseEscolar ------------------------------------------------------------------------ PaseEscolar's Profile: http://forums.techarena.in/members/166005.htm View this thread: http://forums.techarena.in/microsoft-project/1284687.htm http://forums.techarena.in This is a multi-part message in MIME format. ------=_NextPart_000_0022_01CA825D.4AD20E10 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi PaseEscolar, Welcome to this Microsoft Project newsgr...

disable macro warning
I added macro which I then deleted. Every time I enter system it asks me if I want to disable or enable macros - how do I turn off this warning screen upon entering system when I have no macros in the worksheet? You need to remove the module that contained the macro. Press ALT+F11, go to View > Project Explorer (if needed), right-click on the module(s), and remove it. Save. HTH Jason Atlanta, GA >-----Original Message----- >I added macro which I then deleted. Every time I enter >system it asks me if I want to disable or enable macros - >how do I turn off this warnin...

Auto Updater #2
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I attempt to install the MS auto updater 2.1.1, I get a message that states my connection to the update server was interrupted, the server was too busy or there was a problem connecting with my network. Has anyone had this issue and how do I resolve this. On 4/6/09 3:07 PM, "dylan204@officeformac.com" <dylan204@officeformac.com> wrote: > When I attempt to install the MS auto updater 2.1.1, I get a message that > states my connection to the update server was interrupted, the server was > too...

Disable sending Email
In Exchange 5.5 in an NT Domain, is there a way to disable an user from sending email? He should be able to open and read his email but not be able to send out email untill permitted. Thanks On Wed, 16 Mar 2005 15:01:03 -0800, "SI" <SI@discussions.microsoft.com> wrote: >In Exchange 5.5 in an NT Domain, is there a way to disable an user from >sending email? He should be able to open and read his email but not be able >to send out email untill permitted. > >Thanks You can do this on Internet mail by setting the Delivery Restriction on the IMS to deny mail...

SMTP being disabled
HI Exchange 2003 / Server 2003 I'm just having a world of problems with Exchange all of a sudden... It's been up and running about 2 months but all of a sudden it's going nuts... The SMTP service is being stopped and set to disabled. This is happening each morning around 1 AM. The only thing I can find in the event logs is event 7035 - The SMTP service was successfully sent a stop control and then Event 7040 - The start type of SMTP service was changed from auto start to disabled For the life of me, I can't find what is originating these commands.... I thought maybe t...

Money 2004: separate dates for transfers between 2 accounts?
Money requires that you have a single date for a transfer between 2 accounts. However, in reality, this is often not the case. For example, when I pay my credit card bill online, the credit card company uses the date I entered the info online (e.g., 10/1/2003). However, the money might not actually be withdrawn from my checking account for a few days (e.g., 10/6/2003). So, from my credit card statement's point of view, the transfer occured on 10/1. However, from my checking statement's point of view, the transfer occured on 10/6. Money simply uses whichever date was input la...

Custom formatting for a cell
I need some help… In Excel 2003, I am entering in a column of cells; different numbers that I want formatted a specific way. For example: I enter this number: 31002770004304330 I want to format them like this: 3100-27-7000-430-4330 After I entered the first series of numbers without the dashes, I went to format cells, selected custom, and enter in 0000-00-0000-000-0000 and applied this format. What happen is Excel changes by last 4 numbers in the string from 4330 to 4300 when I enter the data. Why? I am using the numeric key pad enter a couple hundred of these and don’t want to ...

MTA Auto Stop ( Error 4284, 4287 )
Dear, I am using Exchange 5.5 SP4 with WinNT4.0 SP6a I find on this two day My MTA Auto Stop, and I need to Restart I check the Event log , It have below Message EVENT ID 4284 , Source MSExchangeMTA An error occured during connection/disconnection. Error code : 8511 ( POP4 POP4 DOWN 6) (14) EVENT ID 4287 , Source MSExchangeMTA An Internal MTA error occured. Contact Microsoft Technical Support. Error code=8640 ( 75 POP4 POP4 DOWN 6) (14) EVENT ID 9277 , Source MSExchangeMTA The MTA has issued an RPC Cancel call for thread 1, Locality Table Index 31, Time (ms); 1240000, Cancel result 0 ( QPL...

Cell formats #3
Is it possible to customise a cell in excel that will display a negative number within parenthesis rather than the entered value preceeded by a minus sign? e.g Value entry "-1500" displays as "-1500" Required display "(1500)" Regards Steve Steve, Yes. Format - Cells - Number. In Category, choose Number or Currency, and you'll have a box to choose the display format of negative numbers. For more control, see Custom. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "steve" <anonymo...

Add date range
I'd like to add a column to my spreadsheet that includes a range of dates by week, i.e. COLUMN A Week 16/02/09 - 22/02/09 09/02/09 - 15/02/09 02/02/09 - 08/02/09 26/01/09 - 01/01/09 etc COLUMN B Week Number 8 7 6 5 etc Is there a function to do this automatically? I'm using Excel 2007 SP3 Keep it simpler, list/fill down the startdates in col A, enddates in col B, from row2 down. Then in C2, copied down: =WEEKNUM(A2) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Jonas Trevellion" <jonas.trevellion@g...

Disable Cached Mode
Hi all! Is there any way to disable Cached Mode (CM) on Exchange 2003? Sure, I can do it with GPO, but some computers can be out of scope of this GPO and then they will be able to turn on the CM in their Offce 2003. But CM is critical security risk and I have to disable it. At http://office.microsoft.com/en-us/assistance/HP010000781033.aspx?Product=out2003 I can read: "Exchange server administrators have the ability to disable this feature in Outlook. For more information, contact your Exchange server administrator. Additional information for administrators is available in the Mi...

Auto Reply Outlook 2003
I have Windows 2003/Exchange 2003/Outlook 2003. I have a general Customer Service mailbox. I would like EVERY email that comes into this box to receive an auto response back saying that we will reply within 24 hours. I do not want an Out Of Office reply, we are not out of the office. And it must be server-level because multiple users access the mailbox and Outlook is not always running on any one user's computer. I read this article on Slipstick http://www.slipstick.com/rules/autoreply.htm but it is not clear whether or not the article pertains to Outlook 2003 and it seems that...

Chart formatting question
I am working on a bubble chart that has 10 points of data that I am plotting. The y-axis scale set up is maximum=14, major unit= 2, minor unit=.4 and the x-axis scale set up is maximum=12, major unit= 1, minor unit=.2. The data the chart represents has the highest value up the y-axis= 10 and the lowest value on the x-axis=4.5. My question is, the chart looks very horizontal. I would love to increase the y-axis somehow so the bubbles look higher. Is there a way to increase the y-axis scale somehow so that the chart looks more square-like? If I am missing something really elementary here,...

days between dates
I have used the excel help but have hit a brick wall. I would like to get the fraction (4/30/2006- TODAY())/365 (or 4/30/2006 - 5/1/2005). When I tried the DAYS360 or DATEVALUE I get either very large or small numbers but no whole numbers for days. I have tried just TODAY()- a date and that also gives a large number. I realize this is probably a simple solution, but I am in the dark. Thanks. GA -- My address is spoofed, so do not reply directly. This seems to work for me when A1 contains 4/30/2006: =(A1-TODAY())/365 ******************* ~Anne Troy www.OfficeArticles.com www.MyExpert...

Missing Date picker in Vista
I have an Access application where I use a Date picker (mscomct2.ocx). I have newly installed Vista Ultimate on my computer and suddenly this control� has disappeared. Is it replaced with something else or what? regards reidarT I don't have an answer about the ActiveX control in Vista but you should probably be using a NonActiveX calendar anyway because of the versioning problems. Jeff Conrad has collected a slew of links for them on his site: http://home.bendbroadband.com/conradsystems/accessjunkie/calendars.html reidarT wrote: >I have an Access application where I use a Date pick...

Draft date stamp
Hi friends: In Outlook 2002 when I create and save drafts in drafts folder there is no date and time stamp on them. When I check the properties of the draft, it doesn't provide me with any information. I am using Outlook 2002 sp2 with Exchange 5.5 sp4 in the back. Please advise. Thanks. Open the mail and choose File-> Properties. It should display a modified date. -- Roady www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Question of the month: How to manage general contact info. www.sparnaaij.net ----- &q...

Colouring in advance dates
Hi Guys Can you help me, I wish to input a date in one column and have a date six months ahead in the next column. I then want the date to change colour depending on the date i.e. if the date was 120 days from the original date it would be green and if it was 150 days from the original date it would turn red. Any help would be appreciated Thanks Elvey Look at conditional formatting. Debra has a decent starter page at http://www.contextures.com/xlCondFormat01.html -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Elvey Middleton" <...