I want to subtract 57 sec. from 1 minute 27 sec. and input 1:27 not 87 sec. help!

This is what I do now

A1=57
B1=87......   But I want B1 to look like this-1:27

=(B1-A1)  

Please let me know the right formula.  Thank

--
Message posted from http://www.ExcelForum.com

0
12/20/2003 6:41:09 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
497 Views

Similar Articles

[PageSpeed] 41

Hi Nave!

You seem to be wanting to have your data in time format but are not
"telling" Excel that. How does Excel "know" that 57 is 57 seconds?

in A1:
=57/(24*60*60)
Format as time (e.g.) h:mm:ss
Returns 00:00:57

in B1
=87/(24*60*60)
Format as time
Returns 0:01:27

Now =B1-A1 returns 0:00:30

Excel records time as a decimal part of 1 day hence to get seconds
from bare numbers we divide by 24*60*60

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"nave" <nave.yq3sy@excelforum-nospam.com> wrote in message
news:nave.yq3sy@excelforum-nospam.com...
> This is what I do now
>
> A1=57
> B1=87......   But I want B1 to look like this-1:27
>
> =(B1-A1)
>
> Please let me know the right formula.  Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
12/20/2003 6:55:48 AM
Hi Nave

As an alternative to doing a calculation you can input times and
Intellisence will format as times

A1 Entry 0:0:57
B1 Entry 0:1:27

B1 entry could be 0:0:87 but then Intellisence doesn't step in and
format as time but gives 0.00100694444444444 which formatted as time
is 0:1:27



-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"nave" <nave.yq3sy@excelforum-nospam.com> wrote in message
news:nave.yq3sy@excelforum-nospam.com...
> This is what I do now
>
> A1=57
> B1=87......   But I want B1 to look like this-1:27
>
> =(B1-A1)
>
> Please let me know the right formula.  Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
12/20/2003 11:31:46 AM
Reply:

Similar Artilces:

Public Folder Help #2
I have set permissions for users on public folders that contain contact groups. They used to work fine. Now when users logged into certain computers users are denied permissions. But they can log in on another computers with the same username and their permissions work fine. What can be causing this problem? Thanks ...

Excel wants to create a page break in every cell!
After saving revisions in an Excel worksheet, I get a popup message that states "Margins do not fit page size". After clicking "ok" (because this is my only choice) a page break is automatically created in ever cell. At times(depending on the sizeof the work sheet) it can be over 100 pages with a page size 14% or smaller. Why does this happen, and how can I avoid it? I saw this suggestion in a different forum. Try closing excel. Clean up your windows temp folder Windows start button|Run type: %temp% and hit enter Clean all those files that you ca...

upgrading Action Pack CRM to 1.2
I recieved Microsoft CRM 1.0 with my Microsoft Action Pack subscription. Does anyone know if the 1.2 update will be available for download. Will we recieve a update in later Action Pack releases. Can Action pack subscribers get access to CustomerSource and download the update. Does anyone know a way to get the update legally. I would like to begin testing 1.2 now. Thanks I spoke with microsoft today and he mentioned that Ver 1.2 will be available in the Action Pack update late January. Len >-----Original Message----- >I recieved Microsoft CRM 1.0 with my Microsoft Ac...

CommandButton Help!
i wanna set it so that when a user double click the Command Button i sets cells J4 to J134 all to False, but i can't figure out how to d this I am using Excel 200 -- Message posted from http://www.ExcelForum.com Hi Private Sub CommandButton1_DblClick(ByVal _ Cancel As MSForms.ReturnBoolean) Range("J4:J134").Value = False End Sub -- HTH. Best wishes Harald Followup to newsgroup only please "Apocracy >" <<Apocracy.11z255@excelforum-nospam.com> skrev i melding news:Apocracy.11z255@excelforum-nospam.com... > i wanna set it so that when a user double...

Question No 1 for Testing Purpose
Question No 1 for Testing Purpose Ignore this "Test" <Test@Test.com> wrote in message news:165D35B4-07E0-4BFC-AB83-05CA2AF68DE1@microsoft.com... > Question No 1 for Testing Purpose > ...

Pleas Help !!! CPrintDialog
// COD CPrintDialog dlg (FALSE); dlg.m_pd.Flags |= PD_PRINTSETUP; dlg.DoModal(); Q : How can I show the 'dlg' with A4 selected not letter on paper size ??????. ...

keyboard recognition has slowed down in excel, help/
When using excell my keyboard recognition slows down to the point I have to sit and wait for a few seconds. Does anyone have any idea why this is happening in Excell? ...

=?iso-8859-1?q?Change_Stressed_Characters_(=E1)_to_Chinese?=
Hi I really got a problem with Outlook. I am Spanish and use Xp and W2003. I use Word to send invoices by email using a template. It sends through Outlook. If the recipient uses Outlook everything is fine, but if it uses Hotmail, then all the stressed letters(=E1 =E9 =ED =F3 =FA) and = the following letter are turned into chinese characters, making the reading a pest. I have tried with different configurations of internet explorer, outlook and word options, but nothing changes. Any ideas? Thanks for reading Possible solutions: 1. Your clients should get a better email program. 2. Don...

Help! Incorrectly addressed email
Hello all At the moment, the company is receiving email with a username@ourdomain.com which you would expect. The only problem is, the username is no one on our domain but the mail is still going to some peoples inboxes. Can someone please explain to me how/why this can happen and what I can do to stop it? We are running exchange server 2003. Many thanks, Is it being BCC'd to them? "Gareth Howe" <no@spam.com> wrote in message news:ek5jkQfUEHA.2356@TK2MSFTNGP10.phx.gbl... > Hello all > > At the moment, the company is receiving email with a username@ourdom...

SBS 2003 help needed
Outlook(R) Mobile Access is supported only on Microsoft(R) Exchange Server 2003. Currently your mailbox is stored on an older version of Exchange server. Please contact your system administrator for additional assistance I get this error trying to access oma on sbs 2003, and it's the only server i've got ! Any ideas ? Jack Hi, I would recommend posting to microsoft.public.windows.server.sbs too. "Jack Nielsen" <no_spam jack.nielsen@get2net.dk> wrote in message news:Oa0Q5sGkEHA.1996@TK2MSFTNGP09.phx.gbl... > Outlook(R) Mobile Access is supported only on Micr...

X=1 Help!!!
I have a spreadsheet where I have a cell (P1) NAMED "Free". In the following range ($P$2:$P$65536) - I will be using X, or x to indicate it's free of charge. I want this to be equal to 1. The formula that I am using is ----- =SUMPRODUCT('[MyRecord Log 2006.xls]Akron-06'!$P$2:$P$65536)--('[MyRecord Log 2006.xls]Akron-06'!$N$2:$N$65536>=--"2006-01-01")--('[MyRecord Log 2006.xls]Akron-06'!$N$2:$N$65536<=--"2006-01-06") The formual works but it is only counting the X,x as one regardless if I have many checked in that date range. ...

Getting 5.6.1 rejected by remote host errors when routing out via connector
Hi, We have an SMTP connector (using a Smarthost, not DNS) in our Exchange 2003 org that will route out mail that it does not find a recipient for for a certain domain in Recipient Policy. Let's call it: domain.com So if no one in our Active Directory has the email address someone@domain.com it will route that mail out via this connector directly to an Exim mail server that currently hosts the rest of the mailboxes (We are in a migration stage, getting the Exim users over to Exchange 2003). It is running Exim 4.43 (that's what it says when I telnet to it). Not all mail is getting t...

Help I can receive but not send
Hi, I've been trying to sort out my email for days and, having managed to send once, I just can't seem to make it happen anymore. I get the following message : Task 'mail.uniquecharm.co.uk - Sending and Receiving' reported error (0x800CCC78) : 'Unable to send the message. Please verify the e-mail address in your account properties. The server responded: ?30 authentication required - "Your email could not be sent. To fix this you must make a simple change to your email (known as SMTP authentication). For advice visit www.btyahoo.com/smtp"' ...

HELP!!!!!
1. How would you make a query that allows users to enter a letter or a few letters and selects all Customers Last Name that have those letters ANYWHERE in the name? 2. How would you make a query that displays dates like this May 2-10 and June 2-10 using the Between Operator? HELP ME PLEASE On Oct 30, 7:50 pm, Puck <P...@discussions.microsoft.com> wrote: > 1. How would you make a query that allows users to enter a letter or a few > letters and selects all Customers Last Name that have those letters ANYWHERE > in the name? > 2. How would you make a query that displays ...

Help syncing email
HOw do you sync email to pda. Can see them in outlook but they want sync to pc. Depends on your PDA and conduit software. Check with the PDA manufacturer. Ellowyn wrote: > HOw do you sync email to pda. Can see them in outlook but > they want sync to pc. Ellowyn <ellowynb1@hotmail.com> wrote: > HOw do you sync email to pda. Can see them in outlook but > they want sync to pc. If you have a Pocket PC, download ActiveSync (which should have been included on your PDA's companion CD) from http://www.microsoft.com/windowsmobile/resources/downloads/pocketpc/activesync...

help with update to Office 2007
Updating computers running Windows XP from Office 2003 to Office 2007. Office 2003 is removed with the install. When the user logs on and tries to open an Office document Office 2003 reinstalls. When Admin logs on Office 2003 is not in the Add Remove Programs. When the user logs on Office 2003 is in the Add Remove Programs, but cannot be removed because they do not have admin privileges. How can I prevent Office 2003 from reinstalling!! ? We do use Roaming Profiles over here. ...

Help to find and replace between sheets
Hi all, i have 2 sheets as below column c J row 6 511 285 row 7 285 row 8 422 625 sheet2 col b m row 100 510 row 105 511 285 row 120 282 row 125 285 row 800 422 625 find sheet1 511 and 422 in sheet2 (because value next to them is > 0) then put next to them 285 and 625 in sheet2 from sheet1 thanks in advance regards, Eddy stan On May 14, 4:59=A0am, Eddy Stan <EddyS...@discussions.microsoft.com> wrote: > Hi all, > i have 2 sheets as below > column =A0 =A0 c =A0 =A0 =A0 =A0J ...

Please Help with Windows Defender Error
Hi, I hope someone can help me. I have read so many documents and tried so many things that do not work. When I turn on my computer I get this error message about windows defender. Error Code 0x800106ba Application failed to initialize.... I found this article "Article ID: 931849 - Last Review: May 4, 2010 - Revision: 3.0" . I tried everything and nothing worked. I got to the last option, #4 and this file wuaueng.dll was not succefull in running. Here is what the last option # 4 says to do. If this issue is not resolved after you complete step 3, follow this step:...

Error 1173 MSExchangeIS --Please help
Hi, I have gotten an error: EventID:1173, Source MSExchangeIS Mailbox. It says error 0x6bb deleting unused restricted view from folder. Is this problem serious? I have gotten these in the past but they went away for awhile. Does this indicate the beginning of database corruption? I appreciate any help... John Kavanagh This probably is not an error, but warning or information, right? Reduce logging level on MSExchangeIS Private. John Kavanagh wrote: > Hi, > > I have gotten an error: EventID:1173, Source MSExchangeIS > Mailbox. It says error 0x6bb deleting unused res...

PLEASE HELP 05-17-10
Ok I am trying to make the document where if I enter "a" in one cell, the cell next to it will auto populate a given value that I define. Please let me know how I can do this!!! Thanks!!! If you have a table of values somewhere which determines what "a" is equivalent to, then you could use a VLOOKUP formula in the next cell. Hope this helps. Pete On May 17, 10:14=A0pm, sagenichole <sagenich...@discussions.microsoft.com> wrote: > Ok I am trying to make the document where if I enter "a" in one cell, the > cell next to it will auto p...

Adobe Reader 9.3.1
I have Adobe Reader 9.3.1 but when I go to Adobe's website, it states the latest version is 9.3-Do I have the latest version? Second, when 'updating' software, is it best to install 'over' the older version or delete the older version and then install? From: "Summer1" <Summer1@discussions.microsoft.com> | I have Adobe Reader 9.3.1 but when I go to Adobe's website, it states the | latest version is 9.3-Do I have the latest version? | Second, when 'updating' software, is it best to install 'over' the older | version or ...

Begginer Help
At the moment I am working on one of the excel file and I found on object in file can you tell me what is it? and how can I insert it. Vira +------------------------------------------------------------------- |Filename: Book2.zip |Download: http://www.excelforum.com/attachment.php?postid=3718 +------------------------------------------------------------------- -- viralbhat ----------------------------------------------------------------------- viralbhatt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2650 V...

Excel help #2
Hi - I'm pretty familiar with most stuff in excel and I'd say I'm an intermediate user. I would like to learn how to do vba in excel. Does anyone know of any online help or tutorials that would teach me? If possible, I would like interactive suggestions. Thanks in advance. Newtechie David McRitchie's "Getting Started". Note links to other sites at the boittom of the page. http://www.mvps.org/dmcritchie/excel/getstarted.htm VBA Tutorial from Microsoft. http://support.microsoft.com/support/excel/content/vba101/default.asp There are many more, but these sho...

I need help creating a form please
I've tried and can't seem to get my form to work. I need the form to have a shipper with address and a consignee with address on it but I would like the shipper and the consignee to have like a address book that the information gets added to. I have my address book tables and address book forms. can anyone help please. Have a look at the Northwind sample database that comes with Access, The Orders form may be what you are looking for. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "summernight" <summernight@dis...

Help!! I have problem deleting 2500 rows of filtered rows!!!!
I have total of 156 columns and 3900 rows with 20 columns of formula for each row. My filtered result is 2500 rows. I tried to delete 2500 rows in my excel but it took me more than half an hour to delete it either by vba code or manually by excel. I tried using office clipboard manually, it paste the data as value and my formula isn't there anymore. Anybody has any idea how to write a vba program to solve this problem and how to write a office clipboard in vba. Please help... anybody... :( DESPERATE FOR THE ANSWER -- shirley_kee ---------------------------------------------------...