Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Minutes from and Time Based Calculation
My spreadsheet has two columns with two different times, as follows:
Col A Col B 11:06 12:09 These columns are formatted as Time xx:xx:xx (although I really don't care about seconds), 24H format. I want column C to show the difference between column A and column B, in minutes, not hours. My problem is that when I use a Pivot Table to summarize the sum of column C, as soon as column C 24 hours, it shows 00:10:00 (ie. 24:10:00) and then the sorting in my pivot table gets all out of whck. Sorry--I suspect this is simple but I can't seem to figure it out. Thanks in advance Don |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Minutes from and Time Based Calculation
I put some time values in A and B
In C I use =B2-A2+(A2B2) In D I enters some 'codes' Made a Pivot Table with 'codes' as column headers I use SUM of time but I had to format the Pivot Table cells with Custom Format [h]:mm to get results in hours & mins (by default I got decimal days) best wishes - hope I understood the problem -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Don" wrote in message ... My spreadsheet has two columns with two different times, as follows: Col A Col B 11:06 12:09 These columns are formatted as Time xx:xx:xx (although I really don't care about seconds), 24H format. I want column C to show the difference between column A and column B, in minutes, not hours. My problem is that when I use a Pivot Table to summarize the sum of column C, as soon as column C 24 hours, it shows 00:10:00 (ie. 24:10:00) and then the sorting in my pivot table gets all out of whck. Sorry--I suspect this is simple but I can't seem to figure it out. Thanks in advance Don |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Minutes from and Time Based Calculation
Thank you, Bernard. That seems to have done the trick
It is interesting, but I have never seen a formula like B2-A2+(A2B2) before. What does the last part of the formula do (A2B2)? Is that a short form for the trapping of negative numbers? Thanks again! "Bernard Liengme" wrote: I put some time values in A and B In C I use =B2-A2+(A2B2) In D I enters some 'codes' Made a Pivot Table with 'codes' as column headers I use SUM of time but I had to format the Pivot Table cells with Custom Format [h]:mm to get results in hours & mins (by default I got decimal days) best wishes - hope I understood the problem -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Don" wrote in message ... My spreadsheet has two columns with two different times, as follows: Col A Col B 11:06 12:09 These columns are formatted as Time xx:xx:xx (although I really don't care about seconds), 24H format. I want column C to show the difference between column A and column B, in minutes, not hours. My problem is that when I use a Pivot Table to summarize the sum of column C, as soon as column C 24 hours, it shows 00:10:00 (ie. 24:10:00) and then the sorting in my pivot table gets all out of whck. Sorry--I suspect this is simple but I can't seem to figure it out. Thanks in advance Don |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Minutes from and Time Based Calculation
Hello Don
In Bernard's formula (A2B2) returns either TRUE or FALSE but when you perform a mathematical function on TRUE/FALSE you get 1/0 so when A2<=B2 the formula equates to =B2-A2+0 which is the same as B2-A2, however when A2B2 (i.e. the second time is the next day) then this equates to =B2-A2+1. Because 1 = 1 day in Excel this is the equivalent of =B2-A2+"24:00" so if for example A2 is 21:00 and B2 05:00 you get ="05:00"-"21:00"+"24:00" = 08:00 Another way to get the same result is =MOD(B2-A2,1) "Don" wrote: Thank you, Bernard. That seems to have done the trick It is interesting, but I have never seen a formula like B2-A2+(A2B2) before. What does the last part of the formula do (A2B2)? Is that a short form for the trapping of negative numbers? Thanks again! "Bernard Liengme" wrote: I put some time values in A and B In C I use =B2-A2+(A2B2) In D I enters some 'codes' Made a Pivot Table with 'codes' as column headers I use SUM of time but I had to format the Pivot Table cells with Custom Format [h]:mm to get results in hours & mins (by default I got decimal days) best wishes - hope I understood the problem -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Don" wrote in message ... My spreadsheet has two columns with two different times, as follows: Col A Col B 11:06 12:09 These columns are formatted as Time xx:xx:xx (although I really don't care about seconds), 24H format. I want column C to show the difference between column A and column B, in minutes, not hours. My problem is that when I use a Pivot Table to summarize the sum of column C, as soon as column C 24 hours, it shows 00:10:00 (ie. 24:10:00) and then the sorting in my pivot table gets all out of whck. Sorry--I suspect this is simple but I can't seem to figure it out. Thanks in advance Don |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Minutes from and Time Based Calculation
This reply is a little late, but I wonder if this is what you're looking for:
=mod(b2-a2,1/24) I didn't put it into a pivot table, so I don't know if it will affect your sort in the same way, but it seems to output the difference in minutes to column C. "Don" wrote: My spreadsheet has two columns with two different times, as follows: Col A Col B 11:06 12:09 These columns are formatted as Time xx:xx:xx (although I really don't care about seconds), 24H format. I want column C to show the difference between column A and column B, in minutes, not hours. My problem is that when I use a Pivot Table to summarize the sum of column C, as soon as column C 24 hours, it shows 00:10:00 (ie. 24:10:00) and then the sorting in my pivot table gets all out of whck. Sorry--I suspect this is simple but I can't seem to figure it out. Thanks in advance Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|