extracting hours from a sum of time

I have a cell which sum a bunch of cells containing an elapsed time.
The sum totals to more than 24 hours.
I have to a cell with a cost per hour and I need to caclculate the
total cost.
The problem is that the hour function returns values in the range of
0-24. My current sum is 25:30 and the hour function return 1 and not
25.
Any ideas how to bypass it?
0
benami (1)
1/29/2008 8:15:38 PM
excel 39879 articles. 2 followers. Follow

1 Replies
823 Views

Similar Articles

[PageSpeed] 54

It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will.
To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and 
a day is stored as the number 1.

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel


<benami@gmail.com> wrote in message news:545744e9-936a-42a6-80d1-fdcf932348d6@i29g2000prf.googlegroups.com...
|I have a cell which sum a bunch of cells containing an elapsed time.
| The sum totals to more than 24 hours.
| I have to a cell with a cost per hour and I need to caclculate the
| total cost.
| The problem is that the hour function returns values in the range of
| 0-24. My current sum is 25:30 and the hour function return 1 and not
| 25.
| Any ideas how to bypass it? 


0
nicolaus (2022)
1/29/2008 8:22:28 PM
Reply:

Similar Artilces:

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Calculate the amount of time over a permitted amount (12 hours)
Hi I would like to calcualte the time over a permitted amount and display it in a cell. Column C is the start time dd/mm/yy hh:mm and Column L is the finish time dd/mm/yy hh:mm. The alloted time is 12 hours for the job and and need column M to show the amount of time used over the 12 hours. Many Thanks. =MAX(L2-C2-"12:00",0) and format as time,[h]:mm -- HTH Bob "Steve M" <SteveM@discussions.microsoft.com> wrote in message news:909E493A-ED55-40A3-9406-6588A333F281@microsoft.com... > Hi > I would like to calcualte the time over ...

Time between two dates
I want to be sure how long has been since 1/18/88 to 9/30/05. If I substract the numbers it says 9/12/17. Can I interpret that as 17 years, nine month and 17 days? It just that it looks as the answer should be 8 months and not nine. Please help. Thank you! Hi! >Can I interpret that as 17 years, nine month and 17 days? Actually, NO! The result you see is due to Excel being "helpful" and using the DATE format from the source cells and applying it to the formula cell. The true result of the formula is 6465 days. When formatted as DATE, 6465 is equivalent to the date of...

Work out overtime hours for individuals
Hi All I have a question which i am hoping someone will eb able to help me with, i have a spreadsheet which contains infromation for part time hours worked during the course of a month for individuals (e.g. below) Name Start End Hours Persona 1:30 2:30 1 Persona 3:30 4:30 1 Persona 9:30 11:30 person b person b person c person c person d etc etc What i need is a formula that will allo wme to add an extra column which gives me the total hours person a worked, person be worked and so on. The original data contains multiple lines (i.e persona could have 10 lines, personb15 lines etc etc), and...

time and date
is there any way to record the date and time in a record so that itwill not change? i.e. i want to record the date and time that an orderis placed. many thanks, michael On 20 Mar 2007 11:42:57 -0700, michael goodall wrote:> is there any way to record the date and time in a record so that it> will not change? i.e. i want to record the date and time that an order> is placed. many thanks, michaelInclude the [OrderTime] field on the form.Code the form's BeforeUpdate event:If Me.NewRecord = True Then Me.[OrderTime] = Now()End If -- FredPlease respond only to this newsgroup.I do not rep...

date/time formatting
I'm trying to duplicate an Excel date/time format in Reporting Services 2005. On the Excel sheet, the difference between two dates is displayed formatted as "150:58:12" and then as "6.29". (12/1/2009 9:41:49 AM - 11/25/2009 2:43:37 AM). Is there a way to format the date/time in Reporting Services to do this? dataGirl You can format dates with .NET formatting yyyMMdd os something like that "dataGirl" <dataGirl@discussions.microsoft.com> wrote in message news:747727D3-CA46-49BE-ACED-F7D5418FE7BF@microsoft.com... > I'm tryin...

Sum of common rows
I am trying to find an automated way of summing common row data. For example: ORIGINAL DATA Row1 123 John Smith Row2 456 John Smith Row3 678 John Smith Row4 324 Dave Jones Row5 678 Dave Jones DESIRED DATA Row1 1257 John Smith Row2 1002 Dave Jones I have several thousand rows and don't want to write something that is specific to the data that appears in the second column. Any ideas? Try this Row 1(col A and B) must have a header like Number and Name It will make a Unique list in Col C and the sum in Col D Sub test() Dim LastRow As Long With Sheets...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

turn working hours into man.days
Hi, in our company, standard working time is 07:30 (or 7.5) hours per day. i would like to know can i convert working hours during month as man.days ? e.g.: if someone worked 15 hours = 2 man.days. format of cells where daily hours are written is : hh:mm:ss and i want to get something like dd.hh.mm as resulting man.days thanks a lot, A. Assuming the total hours are in A1, use =INT(A1*24/7.5)+MOD(A1*24,7.5)/24 and format the cell as d:hh:mm -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alain R." <nospam@noemail.com>...

when i change data in a sum forumla, the sum doesn't change?
i am doing a simple sum formula, using it several times in the worksheet, and it used to work fine, however, suddenly now the formulas are only working after the initial calculation, if i go back and change data later on, the formula doesn't reflect any changes and special pasting of the formula only reproduces the value of the original calculation! I want the Sum function to work as it should. Reflect changes as they are made and be able to paste a function to another group of cells! Under Tools->Options->Calculation make sure Automatic is checked "richard_kta"...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Please help me with run-time error '6' Overflow.
Hi, I am getting an overflow error. Below is the code. Please help me with the fix. I am actually comparing data of two sheets and printing Matched/Not Matched in the third sheet. Sub compare() Dim j As Integer, k As Integer Set rngSheet_res = ThisWorkbook.Sheets("Compare").Range("A2:BL1000") For j = 2 To 1000 For k = 1 To 100 If ThisWorkbook.Sheets("RC").Cells(j, k).Value = ThisWorkbook.Sheets("FM").Cells(j, k).Value Then ThisWorkbook.Sheets("Compare").Cells(j, k).Value = "Matched" Else ThisWork...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

Extracting data to make mailing list
My neighbour has a .PUBS name and address doc. The doc is 10 pages long. Each page of the document is a table with 10 rows and 3 columns. Each cell is name on top the line followed by 2 or 3 lines of address. She then just cuts the full sheet Avery into mailing labels. This has become too difficult to manage, sort, filter, etc. Anyone have a suggestion how to extract this data so I can make a Publisher mailing list? Thanks. -- Ross try bringing it into Excel...you'll some "massaging" to do. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Ross"...

Summing multi-level bill of material
All, I have a multi-level bill of material and I want to create a function that will sum it properly without having to sum up each level manually. Here is an example: Level Item Quantity Cost 1 Chair 1 $39 2 Seat 1 $15 3 Cushion 1 $10 3 Base 1 $5 2 Leg 4 $1 2 Back 1 $20 3 Leather 1 $12 3 Wood 1 $8 In my example, I have a chair. The chair is composed of a seat ($15), four legs ($1 each), and a back...

MID formula not extracting what I want.
I'm trying to extract the date from a cell using the MID formula and all I'm getting is some weird number that does not seem to make sense. I have tried formatting the target cell but still no change. Any clues about it? How can I do this? 1/13/2006 12:16:29 AM =MID(E5,1,9) 38730.011 -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860 View this thread: http://www.excelforum.com/showthread.php?threadid=501062 Hi That's because Excel stores dates as...

Workflow email: Lead converted within 24 hours
Hi, I'm having trouble figuring out how to make a timed workflow email based on whether a condition's been satisfied or not. I've tried tips from the "Using MS CRM" book as well as searches within this group. Here's what I'm going for and my progress: We need an email to be sent out when a Lead isn't converted within 24 hours of an Owner receiving it. I think 24 hours after creation would be acceptable to start with, but Ownership would be even better. The closest I can come is a workflow that says: When Lead Status is Changed Wait for 1 day after Le...

can multiple threads calling the same dll at the same time?
can multiple threads calling the same dll at the same time? Is it thread safe? >can multiple threads calling the same dll at the same time? Sure. >Is it thread safe? It depends if the code you're calling in the DLL is. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq ...