how to calculate seconds elapsed between two moments?

I need the number of seconds between two moments.
A1: 14:10:00
A2: 14:15:00

Difference is 5 minutes = 300 seconds.

What formula(/cell format) do I need to use to get the number 300 in
the result cell? When I use the formula A2-A1 I get a
00:05:00 (in time format)
0.003472222 (in General format)

When I use Second(a2-a1) I get 0

how to get 300 ??

Wim

0
eisingspam (27)
8/26/2005 9:11:53 AM
excel 39879 articles. 2 followers. Follow

4 Replies
462 Views

Similar Articles

[PageSpeed] 53

Custom format the cell containing the formula to:

[ss]

The square brackets prevent the seconds from rolling over to minutes and /
or hours when they're greater then 60.
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Wim" <eisingspam@iafrica.com> wrote in message
news:1125047513.606133.9360@g44g2000cwa.googlegroups.com...
I need the number of seconds between two moments.
A1: 14:10:00
A2: 14:15:00

Difference is 5 minutes = 300 seconds.

What formula(/cell format) do I need to use to get the number 300 in
the result cell? When I use the formula A2-A1 I get a
00:05:00 (in time format)
0.003472222 (in General format)

When I use Second(a2-a1) I get 0

how to get 300 ??

Wim


0
ragdyer1 (4060)
8/26/2005 9:35:33 AM
brilliant. No reference to this [ ] format in the MS Excel help files!
Strange.
Thanks a million.
Wim

0
eisingspam (27)
8/26/2005 9:47:26 AM
However, it is just *formatting*,not the real number, hence giving a
problem wen referring to this cell in another cell. I rather use the
formula Minute(a2-a1)*60 + second(a2-a1)

0
eisingspam (27)
8/26/2005 9:52:02 AM
format the cell as
[s]

Format > Cells > choose custom and enter the above format

Mangesh



"Wim" <eisingspam@iafrica.com> wrote in message
news:1125047513.606133.9360@g44g2000cwa.googlegroups.com...
> I need the number of seconds between two moments.
> A1: 14:10:00
> A2: 14:15:00
>
> Difference is 5 minutes = 300 seconds.
>
> What formula(/cell format) do I need to use to get the number 300 in
> the result cell? When I use the formula A2-A1 I get a
> 00:05:00 (in time format)
> 0.003472222 (in General format)
>
> When I use Second(a2-a1) I get 0
>
> how to get 300 ??
>
> Wim
>


0
8/26/2005 9:58:49 AM
Reply:

Similar Artilces:

Adding a second Manager field to the User record
We have personnel that report to more than one boss. Is it possible to create a second "Manager" field on the User record that performs just like the original Manager field through the standard Customization interface? Hi Robin, It seems like it's possible to make such customization with CRM "internal" instruments. You just have to go to customization area in CRM and open the User entity. The attribute name of Manager field is parentsystemuserid, and you can create similar attribute with lookup type and give it a name something like second manager. Then do the ma...

"A configuration error.. caused the message to bounce between two servers"
For a few weeks now I've been trying to debug the connection between two exchange 2k servers on my network. A new one and an old one. Users on the old server couldn't send messages to users on the new one and vice versa. I seem to have partially fixed the problem by Setting our two internal MX records to have the same metric or weight or whatever you want to call it. Now the origional server can send messages to the new server. The new server however still cant send messages to the old server. Anything you try to send in that direction instantly gets the following message...

Probably a simple "IF" calculation, but ???
Looking for some help. I am trying to set up an "IF" statement to look at a value and if it is => then enters another value in another cell. This is what I have done =IF(D54>=61,5,IF(D54>=71,10,IF(D54>=81,15,IF(D54>=91,20,0)))). Now, when I enter a value under 61, then the cell does show "0". But above the number 61, it always is showing "5", when I want it to show the following. => 61, should show 5 => 71, should show 10 => 81, should show 15 and so on. Must be something stupid I am missing here ?. Anyone help me out ?. -- Daywalker...

how do you compare two dates to find the difference?
mm/dd/yyyy is the format. I need to subtract two dates to find the difference Hi =A2-A1 and format the result as 'Number' -- Regards Frank Kabel Frankfurt, Germany Todd wrote: > mm/dd/yyyy is the format. I need to subtract two dates to find the > difference With dates in A1 & A2, in A3: =A1-A2 This will give you the number of days difference. Format A3 as general "Todd" <Todd@discussions.microsoft.com> wrote in message news:19B34084-E5BF-4D21-BB4E-DE60DB66CAE3@microsoft.com... > mm/dd/yyyy is the format. I need to subtract two dates to find the ...

Time calculation 01-21-10
Hello, I am working on the employee attendance data entry the time in and out are based on short time format i have created a report sorted by employee ID and by making the sum of total hours per day in order to get how many wrking hours each employee have im getting a wrong calculation Example ID Employee date totalk wrking hours 1 Tia 1/1/2010 16:00 1 Tia 1/1/2010 16:00 1 Tia 1/1/2010 16:00 1 Tia 1/1/2010 16:00 1 ...

Calculating time and pay in excel
Hi, i'm trying to make a spreadsheet that can calculate pay per minute depending on an average length of time worked. Its driving me crazy and any help would be greatly appreciated! Coloumn A is length of time, at the bottom is a total time, next to that is an average length of time worked and this is then multiplied by the amount appropriate for that average. What would be the best way to work all of this out accurately? Many thanks for any help you can give Mark -- wobbleman ------------------------------------------------------------------------ wobbleman's Profile: http://www.e...

Combine Two Colums
Hello All Need help with the script below or maybe a better one. I need to combine the items in Column A and Column B into one Column The one below will combine the data into one column but it is backward, I need the text in column B to be added after the text in column A Thanks for any help Scott Sub addData() For r = 2 To 100 Cells(r, "D").Value = "@website.com" & Cells(r, "D").Value Next r End Sub This is what I have A B C D E 1 Jimmy @website.com 2 Jimmy @website.c...

booklet printing
I have been sent a publisher document which I need to print as a booklet, but when I try to do this the middle pages come out upside down. anyone else had this problem?? Jill wrote: > I have been sent a publisher document which I need to print as a booklet, but > when I try to do this the middle pages come out upside down. anyone else had > this problem?? If your printer is automatically printing double sided, you need to change the options for the duplexer in your printer driver to from "booklet" to "tablet" (or vice-versa) or "long-edge" to &qu...

calculate net income i.e. revenues (-) expenses in a pivot table
Hello, I need to calculate net income i.e. revenues (-) expenses in a pivot table. My source data is all positive amounts. I do have a field that identifies the amount as a Revenue or Expense. Source fields are: 1.. month/yr 2. Section 3.GL Number 4.GL Line item description 5.$_amount 6 Rev_Exp _ID Once the pivot table is setup how can I get a TOTAL calculation that is revenues (-) expenses by Section by month/Yr? Column fields are: Month/Yr Row fields are: Section, GL Number, GL line item description, Rev_Exp_ID Data is Sum of $_amount. Thanks JB. Try creating a &#...

Date calculations
is there any way I can get 00/00/0000 to be used as a default value in a date field (Table and form). Thanks dave Dave wrote: > is there any way I can get 00/00/0000 to be used as a default value > in a date field (Table and form). > > > Thanks > dave In a DateTime field, no. What's wrong with Null for the default? -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com On Tue, 18 Dec 2007 08:59:09 -0800, "Dave" <dave@accessdatapros> wrote: >is there any way I can get 00/00/0000 to be used as a ...

When and Why Trendlines may not be calculated correctly?
I have been trying to analyse some non financial data with Excel. I did pay to do an Excel course of several weeks, but they only touched upon accounting uses but nothing on statistics. I am very much a novice with Excel. I have beening doing line graphs combined with trendlines. I recently read that trendlines are not accurate unless scatterplots are used. So can someone give me a rough guesstimate what the range of that inaccuracy might be? And a very brief idea of what might cause that inaccuracy? TIA, Nev. Nev - A scatter chart uses real data values for the X coordinate. The erro...

bcwp calculation
I cannot seem to figure out how MSP calculates BCWS and BCWP. I know what Help says and I know how the calculations should be done, but when I try to use the EV elements to calculate BCWS and BCWP on even a single task, I cannot make it match. I know % Complete is calculated as Actual Duration / Duration, right? And BCWS = % Complete * BAC, right? But BCWP is also calculated using % Complete * BAC. But I am sure it is not the same % Complete element. Isn't there a % Complete that uses the number of days of duration since the task began? When I try to "back" i...

Calculate percent for values in detail section
In addition. to the detail section with "District", I have one group section "Country". My report looking something like this: ---------------------------------------- Country District Amount Percent ---------------------------------------- Norway A 8 80% B 2 20% Sweden B 4 40% C 3 30% D 3 30% My problem is the Percent column. I want to...

Manual Calculation Mode
What is the purpose of this feature. David, One circumstance in which manual calculation is useful is when entering data in large workbooks that require long calculation times. In manual mode, one can enter data without having to wait for a recalculation after each entry. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "David" <anonymous@discussions.microsoft.com> wrote in message news:002b01c3dac3$bb6174b0$a401280a@phx.gbl... > What is the purpose of this feature. Chip; Thought I'd take this opportunity to re-...

crm 3.0 use the second email address in my email templates
I am trying to use the second email address of a user in crm as the sender in my email templates. However, I can not find how to select which email address I want to use. I looked the workflow manager and the email template in crm, I did not find anythiing. Regards I'm experiencing the same problem. I also tried to do this with the SDK, a program that sends an e-mail to a contact with the secord emailaddress. But that doesn't work because CRM takes the first address as a standard. If someone has a solution this would be very helpfull. if it all works without errors then ...

Form
Hi I am using Windows XP, and Access 2000. I am trying to create a form that includes fields from two tables. In other words, the form has two text boxes that must display data from one field in one table and one field in another table. I've been trying for ages to make this work. Thank you for your help in advance. Richard Hi Richard, There's not a lot of detail in your question, so here's a general answer: A form can only be bound to a single recordsource (table or query). If you want/need to display fields from two separate tables, you will need to...

Two Outlooks, one mailbox
I have two computers, each with Outlook 2002 accessing the same pop mailbox. They both can access this pop mailbox seperately but when I have both open, the second to connect to the mailbox will pop up a password prompt despite the correct password being entered. Is this a limitation of Outlook or should I contact my ISP? Thanks. Rob Should work, unless you're trying to connect at the very same time or you have it set to conncet always.... Eric W MCP >-----Original Message----- >I have two computers, each with Outlook 2002 accessing the same pop mailbox. >They both can ...

How many quarters between two dates?
Hi Everyone! Got a date question for you. I was wondering if anyone knew of formula where I could do the following: Withing a column, find the oldest date, and the most recent dat listed. Then, find out how many quarters (three months) fell betwee those dates. Anyone know?? Any help is of course greatly appreciated. Thanks! -- Message posted from http://www.ExcelForum.com try: =DATEDIF(MIN(A3:A8),MAX(A3:A8),"m")/3 "Rich9016 >" <<Rich9016.14vysl@excelforum-nospam.com> wrote in message news:Rich9016.14vysl@excelforum-nospam.com... > Hi Everyone! &...

How do I move a decimal over two places?
I want to make 15.861 become 1586.1 What is the easiest way to do this? Thank you --- Message posted from http://www.ExcelForum.com/ Hi Nave! Insert a helper column =A1*100 Select the revised data Copy > Edit > Paste Special > Values > OK Now decide whether you want to keep or delete the original data. -- 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. On Sat, 24 Jan 2004 00:49:05 -0600, nave <<nave.10ixbr@excelforum-nospam.com>&g...

Can't open two Documents with same name !!
Hi: I install Office 97 in WinXP, works good except when I open a excel spreadsheet a windows always opens saying Can't open two Documents with same name. I click ok and everything works OK. How do I stop this warning I get all the time when I first open a Spreadsheet. Thanks Debra Dalgleish has a list of frequently asked questions at: http://www.contextures.com/xlfaqIndex.html This one sounds like yours: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Owen wrote: > > Hi: I install Office 97 in WinXP, works good except when I > open a excel spreadsheet a windows alway...

vlookup from two sources
[Excel 2003] Is it possible to use syntax to perform a vlookup from a source and then another if the first is False? For example, My primary table of data is called "Materials" and my second source is a range A81:E140 on the same sheet as the vlookup, So something like : =vlookup(A1,Materials,2,vlookup(A1,A81:E140),2,False) Can anyone help with advice on syntax? Thankyou, Roger the false in a Vlookup is the mtach type, not an value to use if the formula is false. =if(iserror(vlookup(A1,Materials,2,false)),Vlookup(A1,A81:E140,2,false),Vlookup(A1,Mate...

something accessing my HD once per second
Sometimes something accesses my HD once every second for long period of time. Is there an easy way to find out what this is? -- Replace you know what by j to email Jan Philips wrote: > Sometimes something accesses my HD once every second for long period > of time. Is there an easy way to find out what this is? Easy is relative... http://www.nirsoft.net/utils/cports.html CurrPorts: Monitoring TCP/IP network connections on Windows http://technet.microsoft.com/en-us/sysinternals/bb897437.aspx TCPView for Windows http://www.wireshark.org/ Wireshark � Go deep. Jo...

Right Mouse Click Calculation Feature
Hello, At work I am able to right-click down at the right bottom of my Excel screen and see the sum of a range of numbers. I cannot figure out how to activate this feature on my home computer. Can anyone help me? Thanks, John Go 'View' on the Toolbar on the top left, 'Toolbars' , Check 'Status Bar' Regards, "John M" <anonymous@discussions.microsoft.com> wrote in message news:185201c4a413$5c0ce7a0$a501280a@phx.gbl... > Hello, > > At work I am able to right-click down at the right bottom > of my Excel screen and see the sum of a range ...

Time Calculation #3
...

2 CDs in my Office Professional 2007. What is second cd for?
I have 2 cds in my case for MS Office Professional full version. What is the second cd for. No instructs to tell me what it is for. I have up to 3 computers to install to. But only 2 cds. Anyone know what the second cd is for? The 2nd CD is for Microsoft Office 2007 business Contact Manager. You don't need to install this if you are not likely to use it. Its purpose is to create a database of your business customers to include details like Name, Address, Business telephone numbers, Mobile/Cell phone numbers, Faxes, emails etc etc. Its size should be 253,502 KB. ...