Formula For Time Duration Calculation

Hi Every One,

I Have excel sheet like,
        A                                                 B                  
                             C
REPORTEDDATETIME	           STATUSDATE                               Result 
(HH:MM)
02/16/2009 04:00 AM	           02/16/2010 04:00 AM                  8760:00
02/16/2010 04:00 AM	           05/16/2010 07:00 PM                   672:00 
03/20/2010 02:00 AM            03/21/2010 02:00 AM                     24:00

I required Result like that column C, Could you please  any one can help me 
on this.
0
Utf
3/21/2010 1:31:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
959 Views

Similar Articles

[PageSpeed] 4

Hi, 

simply subtract like

=B1-A1

and format the result with custom format 

[hh]:mm

regards, teylyn



Deen;677392 Wrote: 
> 
Hi Every One,
> 
> I Have excel sheet like,
> A                                                 B
> C
> REPORTEDDATETIME	           STATUSDATE                              
Result
> (HH:MM)
> 02/16/2009 04:00 AM	           02/16/2010 04:00 AM                 
8760:00
> 02/16/2010 04:00 AM	           05/16/2010 07:00 PM                  
672:00
> 03/20/2010 02:00 AM            03/21/2010 02:00 AM                    
24:00
> 
> I required Result like that column C, Could you please  any one can
help me
> on this.


-- 
teylyn

Teylyn  --  'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=189259

http://www.thecodecage.com/forumz/chat.php

0
teylyn
3/21/2010 1:48:22 AM
hi
see your other post
regards
FSt1

"Deen" wrote:

> Hi Every One,
> 
> I Have excel sheet like,
>         A                                                 B                  
>                              C
> REPORTEDDATETIME	           STATUSDATE                               Result 
> (HH:MM)
> 02/16/2009 04:00 AM	           02/16/2010 04:00 AM                  8760:00
> 02/16/2010 04:00 AM	           05/16/2010 07:00 PM                   672:00 
> 03/20/2010 02:00 AM            03/21/2010 02:00 AM                     24:00
> 
> I required Result like that column C, Could you please  any one can help me 
> on this.
0
Utf
3/21/2010 1:55:01 AM
Reply:

Similar Artilces:

Clear data without deleting formulas
We use several spreadsheets on a monthly basis, so every month must delete the data that was input for the prior month to put in the new data. However, we also need to be careful not to delete any of the formulas while we're at it. What is the best way to clear out data without deleting the formulas too? Deleting contents row by row (or column by column) takes too long. Regards! Winston Select your range of cells that can be cleared. Be careful not to select too much (but you can eliminate formulas later). Now with that range selected: Edit|Goto|Special|Constants hit the delete...

IF Stmt. to calculate cumulative commissions
Folks, I am trying to write an IF statement that will do the following: It calculates a commission amount based on 3 level of Sales: If Sales is < = $1,500, calculate 0.0025*1500 If Sales is < = $3,000, calculate 0.0045*3000 If Sales is > $3,000, calculate 0.0050* cell reference... Now, this may look easy enough..here's the trick that needs to happen Say, sales is $5,500..the commission calculated should be cumulative i.e. 0.0025 * 1500, + .0045*3,000 + .0050*(remainder amount) What would be a clean way of doing this? I will appreciate any help. Thanks. Regards, Shams...

Transaction keeps downloading even after accepted multiple times
I have a checking account which downloads automatically / updates automatically when I start Microsoft. There is one transaction from 11/12/2007 that downloads every time, even though I have accepted it or matched it with a transaction and it still keeps downloading. What do I do? I called the bank and they say it is a Microsoft problem. Please advise. In microsoft.public.money, Connie wrote: >I have a checking account which downloads automatically / updates >automatically when I start Microsoft. There is one transaction from >11/12/2007 that downloads every time, even tho...

Excel formulas 01-06-10
I have a worksheet that we would like to use to calculate price + labor for work. The price formula has me stumped. I would like to be able to put in the price in one box and have the formula determine the amount at which to multiply it by. For example: Price = 20.00 then the formula would determine how many to multiply it by (3.25) from the range given below. Range is 0-2.00 *4 2.01-5 *3.75 5.01-10 *3.5 10.01-20.00 *3.25 20.01-40 *3 40.01-60 *2.75 60.01-100 *2.5 100.01-150 *2.25 >150.01 *2 I have the labor formula and then the total is a sum formula. Please help...

Is there Formula to Tab to certain cells
Is there a formula to tab through certain cells? I have a form with about 15 cells that I would like to access easily through the tab key....is there any way to do that? One way to explicitly control the exact "next cell of focus" selection, is to select the cells in the desired order of travel, and then preserve this ordered movement by creating a named range. This old post describes the steps that can be taken to create such a "named range". http://tinyurl.com/39vzv -- HTH, RD ============================================== Please keep all correspondence within ...

need help with formula for a newbie excel guy
this might be a stupid question to all of you excel experts out ther but please help me. cell c14 = 0 cell d14 = 5 in order to get an N/A in the equation cell, here is the formula that put it. =IF(C14=0,"N/A",D14/C14-1) is it possible to get a formula to have N/A if either cell is "0"??? please help, thank you -- ben80 ----------------------------------------------------------------------- ben803's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3513 View this thread: http://www.excelforum.com/showthread.php?threadid=54896 Instead of ...

Customer Stats
I have a spreadsheet that logs the time/name/enquiry/etc of Customers. I need it to count the number of callers within each hourly period ie 9:00, 9:10, 9:20, 9:25 would be 4 callers etc, for statistical analysis. I need to send this info to a graph showing volume of callers over the day. I am at my wits end - i am sure it is sooo simple. :confused: Thanks --- Message posted from http://www.ExcelForum.com/ Dear Scotty A pivot table may be the answer. Use the time of day as the row field, and then group all of the calls into hourly blocks. Hope this helps Paul >-----Original M...

Show formula result in another sheet
sheet1 A:A contains a formula with some cells returning a value sheet2 A:A references sheet1 A:A with a simple formula: =sheet1!A1 above formula filled down in sheet2 A:A the problem is it is not showing the results from sheet1 anyone know why that should be? Sorry false alarm, I had calculation set to manual. Results are coming back as they should. Red faces all round. "Gotroots" wrote: > sheet1 A:A contains a formula with some cells returning a value > > sheet2 A:A references sheet1 A:A with a simple formula: > > =sheet1!A1 > ...

Margin Calculation
I am looking to create a formula where when the total cost of goods in known, I can input a desired margin and it will give me the sell. So if margin= (sell-cost)/sell*100 Help! --- Message posted from http://www.ExcelForum.com/ One way: Assuming cost in A1, sell in B1: =(1-A1/B1)*100 In article <kbulm.1cops1@excelforum-nospam.com>, kbulm <<kbulm.1cops1@excelforum-nospam.com>> wrote: > I am looking to create a formula where when the total cost of goods in > known, I can input a desired margin and it will give me the sell. > > So if margin= (sell...

override GMT time on all outbound emails
It appears that outbound emails sent by CRM (delivered through the SMTP server) ignore the timezone settings on the CRM server and always set the message with an origination time based on GMT. How can this be changed? Emails look very unprofessional when the "Sent" time seems to be many hours different from the actual local time. It makes it look like either the CRM user is working at strange hours or is taking forever to reply to a client. ...

FORMULA HELP! Find all values in column at random rows and perform calcutlation.
ex. A 1 2 3 3.9% 4 5 6 -2.7% 7 8 9 6.5% 10 11 12 1.2% .. .. .. 1000 Total of formula here. I don't know what row a % number will show up on col A but it is before row 1000. I would like row 1000 under column A to have a formula that says look for any row in column A and if you find a number, add 1 to that number and then find the next one and add 1 to that number and then multiply those two together and then find the next one add 1 then multiply to the last set...etc. Bascially, A100 would have the formula (1+A3)*(1+A6)*(1+A9)*(1+A12)...e...

Freight Rate Calculator
When entering a sales order in Great Plains, we would like a tool to calculate the freight based on zip, dimensions and weight and write that amount into Great Plains. Any third party product out there that can do this? Have you looked at Vship from Vsync? or Starship? Are you shipping via UPS, Fedex or over the road carrier? Brian Business Microvar, Inc. >-----Original Message----- >When entering a sales order in Great Plains, we would like >a tool to calculate the freight based on zip, dimensions >and weight and write that amount into Great Plains. > >Any third...

Need help with a simple Time calculation
Hi I'm a little new in Excel, I need some help with at sheet, that are able to calculate time something like this: text1 3:30 (3 min 30 sec) text2 2:10 3:40 (result of text1 and test2) text3 1:40 8:20 (result of text1, 2 and 3) total 7:20 Thanks in advance if the fields have times in them as they appear, just add them like any other cells and format as mm:ss or hh:mm:ss Regards Trevor "Bjarne Hansen" <bhansen1@hotmail.com> wrote in message news:42ed31aa$0$7385$ba624c82@nntp02.dk.telia.net... > Hi > > ...

Date Formulas #3
How can I make a cell correct the year of a date Hi Charlene not really sure what you're after here if you have 1/1/05 and you want to display just 2005 then right mouse click on the cell and choose format cells / on the numbers tab, choose custom and then type yyyy in the white line and and click OK. however, this just displays the date showing only the year, if you want to extract the year only to another cell you can use =year(A1) where A1 contains the date if you're after something else, please type a few examples of the data you have and what you want to see. Cheers JulieD...

How to copy & paste a worksheet but change 1 bit of the formula?
I want to copy a worksheet names WK 15 that has several formulas in linking to WK 15 of other sheets, then paste it to create another workbook but changing all the formulas to WK16 and not WK15 but keeping all of the other data within the pasted formula the same. EG: SUM('Y:\AREA\PARADE\[WK 16.xls]Sheet1'!D$6) and various similar formula to be copied and paste onto a new workbook but change [WK16.xls] to [WK17.xls] only within all of the formula so I do not have to keep manually changing each cells formula myself. You can create a copy of the worksheet by holding the C...

How is Return for Period calculated?
I am using Money 2003 and I did a Performance by Investment Account report for 2004. For most of stocks the Return for Period column is the Realized Gain/Loss + the Gain/Loss of your current holdings. However for some stocks the numbers don't seem to add up. For instance the realized gain is $400, gain of what I currently have invested is $500, and the return is reported as about $100. Could someone explain to me how this is calculated or if it's a bug. Thanks! Jon ...

Graphing Time
I have a datasheet with time values that are in minutes:seconds format and I need to graph them in some way. However, the only way i am finding to graph them is by manually rounding the number to just minutes. Manually rounding the numbers will not work in the long run because I need to have an automated process and no formula will work with my numbers. here are some of my numbers in (minutes:seconds) 00001:40 00006:40 00013:20 00020:00 00005:00 00023:20 00008:20 That is the way they are given to me and I would like to use them in that format however I can't figured out how to have those...

Please Help Running a Macro from a formula
Is there a way to cause a macro to run from a formula? As an example: =if(B1<B2,GoToNeg,GoToPos) GoToNeg and GoToPos are macros which will manipulate B1 and B2. Thanks in advance! Vic, If they really are macros that alter things on a workbook, you can't do it. If they are functions that return a value, then that is possible. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Vic" <anonymous@discussions.microsoft.com> wrote in message news:014c01c3a56b$af91d680$a601280a@phx.gbl...

Resolving Polynomial Trendline Formula for Chart
How would the following Chart equation be resolved in Excel: y = 147832x2 - 150195x + 2E+07 Thanks! Please explain what you mean by "be resolved" To get the trendline values into cells see http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "soccerkatie" <soccerkatie@discussions.microsoft.com> wrote in message news:FA5E6D19-13E3-40CB-8238-6B4AF88A26F2@microsoft.com... > How would the following Chart equation be resolved in Excel: > > y = 147832...

Fairly intricate array formula question.
I have the following array formula, works correctly for me in cases where both input values are nonblank, but i don't get the result i need if one or the other is blank. Here's the formula: {=SUM((data!A11:A61-AVERAGE(data!A11:A61))*(data!B11:B61-AVERAGE(data!B11:B61)))} [Actually if you are interested, it is the crossproduct term, the numerator of the formula given the help file for the Excel SLOPE function]. What i want the formula to do is skip any row in which a value in range A11:A61 OR B11:B61 is blank, and continue to return the crossproduct sum for the rest of the rows...

if formula #5
i have data in sheet 1 and if the column AG has agent code 1,2,3,4, and so on i have creatred different sheets for the agents i want when the data is inputed in sheet 1 and when column AG is updated the information should be copied to respective sheet. -- Nisha P Hi -Navigate to the cell in the sheet that needs to update automatically -Press the equals (=) key -Navigate to and click on the cell that contains the value required -Press Enter -- Steve "nishkrish" <nishkrish@discussions.microsoft.com> wrote in message news:B0753B6E-DFFA-4DEF-BF89-D65A8C2424D0@microsof...

FORMULAS #35
WHERE CAN I GET A LIST OF ALL FORMULAS MICROSOFT EXCEL USES? Excel's help for worksheet functions? Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ If Peter's site isn't working, but Ron deBruin has a copy at: http://www.rondebruin.nl/files/xlfdic01.zip Norman Harker has his version at Debra Dalgleish's site: http://www.contextures.com/functions.html BLUPHISH wrote: > > WHERE CAN I GET A LIST OF ALL FORMULAS MICROSOFT EXCEL USES? -- Dave Peterson ...

Control formula calculation
I have created a "form" where a user enters 2 separate numbers. I wan each of these numbers to input to a running total and the total t subtract from another number... this is what I have come up wit (understand it is a circular ref ..) 1 2 User input 2 User input 2 Prior input 0Prior input 0 Total input 2 Available 36 Remaining 3 -- Message posted from http://www.ExcelForum.com ...

Formula help please #8
Hi, Can you help? in Worksheet 'Stats' cell H8 I want the value of cell R8 in worksheet 'Log' to be shown, but if the value in cell R8 in worksheet 'Log' is zero (0), then I want the cell H8 in 'Stats' to be blank. Just to complicate things (if it does), there is already a formula in cell R8 of the 'Log' worksheet, which is =COUNTIF(Log!$A$3:A34852,"1") Go easy as I'm the novice Cheers for help =IF(Log!R8=0,"",Log!R8) -- Kind regards, Niek Otten Microsoft MVP - Excel "Anthony" <Anthony@discussions.microso...

template or formula for start time -finish time -total hours ple
please could I have a formula or function to enter start time-end time and show total hours/mins for excel =B1-A1 =B1-A1+(A1>B1) =MOD(B1-A1,1) with start time in A1 and end in B1 using excel time format hh:mm:ss the first 2 formulas work if start is before and end is after midnight -- Regards, Peo Sjoblom "cc" <cc@discussions.microsoft.com> wrote in message news:CD5F3A3B-7101-441C-A18A-93DE69C2D31C@microsoft.com... > please could I have a formula or function to enter start time-end time and > show total hours/mins for excel ...