hours and minutes calculation

does anyone know how to make a spread sheet formulae to 
calculate hours and minutes, which reports in jours and 
minutes?
0
anonymous (74722)
7/6/2004 8:03:20 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
274 Views

Similar Articles

[PageSpeed] 32

Hi Charlie!

Try:
=INT(B1-A1)&" jours "&ROUND(MOD(B1-A1,1)*(24*60),0)&" minutes "

I've rounded the minutes to the nearest minute.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au 


0
njharker (1646)
7/6/2004 10:45:32 AM
Reply:

Similar Artilces:

Mortgage APR Calculation
Hi I am looking for a program that automatically calculates the APR on a loan - can't find it in the Excel functions. Cell A has Loan Amount Cell B has % Interest Rate Cell C has closing costs Where can I find the APR formula? Thanks Cindy Hi Cindy! By APR I understand you to mean Annual Nominal compounded monthly. [I have to specify this as in the UK one legislative definition of APR is the Annual Effective Rate) I have: The loan in A1 [100000] The Nominal compounded monthly in A2 [5%] The Closing fees in A3 [2000] The term in A4 [120] =RATE(A4,PMT(A2/12,A4,A1,0,0),A1-A3,0,0,0)*1...

work out how many trucks came in between the hours of 9am and 3pm
hi there l am after a formula which will calculate how many trucks came in from the hours of 9 am and 3 pm on any day. the main information is in sheet 1 and l want the value to return to sheet 2. How do l do this? and what formula is best to use. A:A time in time out transport coompany amount of pallets 1-Apr 4:00 5:26 LEOCATAS WJT982 BRIAN B 68 TATURA 656223 T15.02 68 1 1-Apr 4:06 5:45 LEOCATAS UMV991 DAVE B 68 TATURA 656224 T13.02 68 1 1-Apr 5:08 6:00 HUNTERS QZW243 RODNEY B 68 TATURA 656228 T17.17 68 1 1-Apr 5:50 6:25 LEOCATA...

Formula for calculating time period between 2 dates
Can someone help me create a formula for calculating the difference between 2 dates: column A: date of birth column B: date of death column C: time period between A and B (C1=B1-A1; C2 = A2-B2, etc.) Thanks. Raj Difference of what ? For Years: =DATEDIF(A1,B1,"Y") For Months: =DATEDIF(A1,B1,"m") For Days: =DATEDIF(A1,B1,"d") Micky "Raj" wrote: > Can someone help me create a formula for calculating the difference between 2 > dates: > > column A: date of birth > column B: date of death > column C: time perio...

elapsed time calculation
I am preparing a worksheet which calculates the length of a telephone cal from one time to another. I have a formula, which will calculate the elapsed time in the form h:mm:ss. I also need to add the total elapsed time of many calls with the goal of expressing the answer as minutes and tenths of minutes, for example the total may be 3,478.4 minutes. Any ideas how I can calculate elapsed time expressed as minutes and tenths of minutes rather that h:mm:ss? -- rwf To get decimal minutes use =SUM(Range)*1440 or perhaps =ROUND(SUM(Range)*1440,2) and format as general -- Regards, Peo Sj...

formula to convert 15 minute to hourly data
I need a formula to convert 15 minute timestep data to hourly average (an average of the four 15 minute data points for the hour). My spreadsheet looks like this: datetime in first cell data in second cell 1/1/00 00:00 2.2 1/1/00 00:15 2.0 1/1/00 00:30 1.9 1/1/00 00:45 2.0 1/1/00 01:00 2.4 1/1/00 01:15 2.2 1/1/00 01:30 2.1 1/1/00 01:45 2.0 1/1/00 02:00 1.8 etc..... I have about hundreds of rows of this type of data. At times there may be missing data, but the correct time is there, there is just no data in the cell. I would like a third column of data that would have the date/time at...

Vista sp1 takes more then 24 hours..
Hello, I've a question... I'm trying to install SP1 for Vista...but its now already more then 24 hours busy. My laptop is restarting every 3 minutes and after that says again : 'fase 3 from 3 0%' and after a few minutes 99% I don't think it's normal that it takes this long, is it? what can i do? (sorry for my bad english) thnx already for the help greets Charley Is SP1 being offered by Windows Update or are you attempting to install it manually? What anti-virus application or security suite is installed and is your subscription current? What a...

DateTime calculation dilemma
I have a database of substation outages that calculates the length of the outage (i..e. TimeOff, TimeOn). Its been working fine for a few years, but we just encountered a situation where a total outage time did not add properly. The total is calculated in hhr:mm format as follows: =Int(Sum([MinutesOff]/60)) & “.” & Format(Sum([MinutesOff]) Mod 60, “00”) It seems this works fine except in the very rare occasion when the total minutes equals exactly 60, which evidentally has never happened before. Then it doesn’t add that extra minute to the total. I’m thinking it must be in ...

interest calculation on variable debt needed
Hi all, hope there's someone out there that has needed to do this I need to run a loan / credit account, the amount of which has the ability to go either up or down due to capital payments or drawdowns therefore it is not an amortised loan, but kind of works as if I am running a credit card..... credit cards don't seem to work out future interest, and loans only allow you to amortise one loan please help am guessing the answer is "you can't do that" then seeing as no response "Stevo" wrote: > Hi all, > > hope there's someone out there th...

Calculated values in a cell
Hello All, I am trying to use this formula =IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",IF(AG3=2,"2011",IF(AG3=1,"2012"))))) to assign a year value to the numbers 1,2,3,4, and 5. These numbers are the product of the following formula: =IF(AF3>2.6,"5",IF(AF3>2.01,"4",IF(AF3>1.42,"3",IF(AF3>0.83,"2",IF(AF3>0.24,"1",IF(AF3<0.01,"Beyond 5 Years")))))). When I write the formula, the result is "FALSE". When I use the same formula in cells with just num...

I want to calculate the average of best 5 out of 7 numbers?
How can I calculate the average of 5 best out of 7.. or best 10 out of 15.. For example if I have 10 different numbers. 10,7,9,8,5,6,7,5,6,7 but I only want it to take the average of the best 7.. I really appreciate your help.. Thank you! Eyad Hi! >if I have 10 different numbers. 10,7,9,8,5,6,7,5,6,7 but I only >want it to take the average of the best 7.. What do you consider to be the best 7? Biff "Attempt at solving a Matrix Problem?" <AttemptatsolvingaMatrixProblem@discussions.microsoft.com> wrote in message news:5CCB1A51-4612-48AA-BF56-C19E46404285@micr...

how can i set excel 2007 to do a "calculate on open"?
When I open Excel itself - it opens fine. When I open an excel file - it will not open until I right click and select 'calculate'. Can anyone tell me why? Or How do I set a default "calculate on open"? Thanks SER It sounds like you have calculation set to manual. I'm not sure how to change it in Excel 2007, but if you copy this in the Immediate window of the VBE and press enter, it should also work Application.Calculation = XLCalculationAutomatic -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "SER" wrote: > When ...

Extract hour:min in Excell or MS query
I am trying to fetch data from Oracle using MS Excel via ODBC. Here is the query I ran on Oracle using SQL*Plus. I want to get the same result in Excel. select to_char(dt_time, 'MM/DD HH24'), sum(value) from tab1 where to_char(dt_time,'Day') not in ('Sunday','Saturday') group by to_char(dt_time, 'MM/DD HH24') Thanks in advance for you quick help.. Max. ...

How to calculate the RATE in annuity?
Hi there, Need your help. On 1 Nov 2005, I bought a unit trust, with initial payment US$5000. Subsequently, each month-end (starting 31 Nov 05), I put in US$200 for a period of two years in a row. On 1 Dec 2007, I will get back US$ 11,000 (including the initial payment) Question: 1) What is the interest rate throughout annuity period? 2) How to use the Microsoft Excel to calculate? Thanks. Please e-mail me directly! cheers dstdst Using the XIRR worksheet function that comes with the 'Analysis ToolPak' add-in To activate, go to TOOLS>ADDINS Check the 'Analysis ToolPak...

recurring meetings times changed by 1 hour
All my recurring meetings that occur during daylight savings time were automatically adjusted by 1 hour, while those occuring during standard time were not. Why? Is there an easy fix and a way to prevent this from happening again? My clock time is correct. ...

how do I set appointments timescale to default to 15 minute interv
Outlook calendar appointsments meeting time dropdown box defaults to selecting appointments at 30 minute intervals, unless you type something else. This is a pain. My apointments are at 15 minute intervals. In the new appointment dialog, is there are way to get the time dropdown box to default to 15 minute intervals? Thanks JM if you are using the day view, right click on the time scale - select 15 min. You can also access the time scale by right clicking on the calendar and choosing other settings. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours ...

Does this slow down calculation?
I want to put some spaces between arguments in formulas to make the more readable but I was wondering if this slows down the calculation time? For example if I have a sheet with many formulas will the calculation times be different between the two examples? IF(VLOOKUP(A1,MyRange,3,False)="yes",Sum(D1:G1),average(B1:C1)) IF( VLOOKUP(A1,MyRange,3,False)="yes", Sum(D1:G1), Average(B1:C1)) I don't think so. In fact, I sometimes add alt-enters to make the formula easier to read, too. sfrancoe wrote: > > I want to put some spaces between arguments in fo...

distance calculations #2
I am trying to think of a formula that will give me the result that I would like: Example Sheet 1 B1=sheet 2 B1 thru B25=sheet 2 B25 C1 equals Sheet 2 Cell B1 as the starting distance D1 equals the distance from the last item to the starting point E1 equals the overall distance Sheet 2 A1-A25 raw data B1-B25 converts raw data to feet My problem is, how can I get it C1 to see whatever the last number in Sheet 2 column B as the distance from the last item back to the source? I was wondering because it’s not always going to be 25 items. > .. how can I get it C1 to see whatever the last ...

Calculating Week Numbers
Hi One of the fields in an Excel timesheet I use calculates the current week number. The formula is : =ROUNDUP(((Q9-DATE(YEAR(Q9),1,0))/7),0) Cell Q9 (referenced by the week number calculation) contains the following formula that calculates Friday's date for the current week : =IF((TODAY()-36651)/7-INT((TODAY()-36651)/7)>0,36651+7*INT((TODAY()-36651)/7)+7,TODAY()) The function works OK, except I usually have to tweak the week calculation formula annually, depending on when the new year starts. Generally changing ROUNDUP to ROUNDDOWN, or vice versa is all that is needed. ...

How to calculate how many cells have a value of less than 3?
What formula can you use to get the total number of entries from within a range of cells that contain any value 3 or less? I have a large numbers of results in a spreadsheet (results can be anywhere from 1-10) and want to be able to easily get the total number of entries that are either 1,2, or 3. Thanks... =countif(a1:b99,"<=3") is one way. FISH wrote: > > What formula can you use to get the total number of entries from within a > range of cells that contain any value 3 or less? > > I have a large numbers of results in a spreadsheet (results can be any...

Converting Minutes to hours and mintues
My question is say i have a number representing minutes how do i convert it to hours and minutes eg/ 140 minutes = 2h20m or something similar, cheers in advanc -- Message posted from http://www.ExcelForum.com Easiest way is to convert to Excel time value (decimal fraction of a day) =minutes/24/60 and format as time. Format|Cells|Number|Custom and enter either [h]:mm or if you want it to appear exactly as "2h20m", then use [h]"h"mm"m" Jerry Branagan1985 < wrote: > My question is > > say i have a number representing minutes > how...

Principal and Interest Calculation
I setup a loan in Money 2004 and, based on the principal and interest calculations shown in the amortization schedule, it appears that Money is using the actual number of days between due dates to determine the portions of the payment that are principal and interest, which of course varies depending on the number of days in the month. However, my bank calculates the principal/interest split based on a standard 30-day month (360 day year). Is there a way to have Money use this type of amortization schedule instead of the one it's using by default? ...

Loan Comparison/Mortgage Calculator
Where did they place the loan comparison or mortgage calculator in 2007? -- David Schwartz Commack, NY Can't say for sure, but they may well have eliminated it. As you've noted elsewhere, the focus of Money development is on "monetizing the relationship" (or trying to make **some** or even ***any*** return on investment in the product, depending on how you look at it) not on features of use for personal finance management. But with the number of such calculators available on the web, why worry it? "David Schwartz" <who4ever@optonline.net> wrote in ...

Textbox Calculation is correct BUT still have problems
I have a member report with a Page Header specifying the member a Member ID Header specifying the column details of member payment record the details of payments with a "AmountPaid" field a Member ID Footer specifying the summary of AmountPaid and whether the member qualifies for additional funding or not by 1 textbox checking with an IIF statement: =IIf([Sum Of AmountPaid]>=[Allowance],"YOU HAVE MET OR EXCEEDED YOUR BENEFIT AMOUNT OF $2000.","You are eligible to receive benefit assistance...

Calculating time in Excel
I am trying to figure out the formula to calculate time, for example: I have 2:00 in the first cell and 4:00 in the next cell, I want to figure out that the difference is 120 minutes. Is this possible? I don't see a direct way, but you can format the cell with the formula as normal and multiply by 24 to get the result in hours, or by 1440 to get the result in minutes. There just happen to be 1440 minutes in a day :-) Matthew "Mandy Quartz" <mandy@certifiedalarms.com> wrote in message news:01b301c34fc0$05fabf20$a501280a@phx.gbl... > I am trying to figure out the ...

Calculating cells #2
while working with a worksheet my excel takes nearly 30 - 40 seconds for calculation and a bar appears at bootom telling calculating cells 5% ,10% like that .What it is why it is taking so much of time for updating a cell If you have lots and lots of formulas, then that could be the problem. If you're doing data entry, maybe turning calculation to manual would be ok. Toggle it back to automatic when you're done with the data entry. Tools|Options|calculation tab is where that setting can be changed. TUNGANA KURMA RAJU wrote: > > while working with a worksheet my excel take...