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

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

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

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

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