Time in Minutes
 

This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

You could use =(G5-C5)*24*60 and format as 00 "min"
--
David Biddulph

"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it worked
great.
I did a paste special and pasted that formula into another cell and it's not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get a return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Thank you for your help - Is that a custom format?

"David Biddulph" wrote:

You could use =(G5-C5)*24*60 and format as 00 "min"
--
David Biddulph

"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it worked
great.
I did a paste special and pasted that formula into another cell and it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Yes.
--
David Biddulph

"Karen" wrote in message
...
Thank you for your help - Is that a custom format?

"David Biddulph" wrote:

You could use =(G5-C5)*24*60 and format as 00 "min"
--
David Biddulph

"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Now it works in that cell - I don't know what I'm doing wrong, but I used the
paste special on another cell and it is not returning the correct value. The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result is
-1393 mins. It must have something to do with the fact that it's AM minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it worked
great.
I did a paste special and pasted that formula into another cell and it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Karen

Excel handles dates and times as whole numbers since Jan 1st 1900 (Day 1),
with times being decimals of a day (.5 is 12 noon), it is just formatting
that makes them look like times.

Your times are crossing midnight and taking no account of the day.

I would direct you here to understand how Excel handles dates and times and
therefore what my original formula is doing

www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I used
the
paste special on another cell and it is not returning the correct value.
The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result is
-1393 mins. It must have something to do with the fact that it's AM minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it
worked
great.
I did a paste special and pasted that formula into another cell and
it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that
then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Hi Karen

One way to deal with times crossing the midnight barrier, is to use
=MOD(G11-C11,1) instead of plain G11-C11

Therefore modifying Nick's formula to
=(((MOD(G11-C11,1))*60)*24)&" min."

or even removing Nick's penchant for brackets ( no offence Nick <bg)
=MOD(G11-C11,1)*60*24 &" min."
will give you the correct answer of 47 min

--
Regards

Roger Govier


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I
used the
paste special on another cell and it is not returning the correct
value. The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result
is
-1393 mins. It must have something to do with the fact that it's AM
minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it
worked
great.
I did a paste special and pasted that formula into another cell and
it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get
a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that
then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Roger...they're parenthesis, not brackets ;-)))))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Roger Govier" wrote in message
...
Hi Karen

One way to deal with times crossing the midnight barrier, is to use
=MOD(G11-C11,1) instead of plain G11-C11

Therefore modifying Nick's formula to
=(((MOD(G11-C11,1))*60)*24)&" min."

or even removing Nick's penchant for brackets ( no offence Nick <bg)
=MOD(G11-C11,1)*60*24 &" min."
will give you the correct answer of 47 min

--
Regards

Roger Govier


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I used
the
paste special on another cell and it is not returning the correct value.
The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result is
-1393 mins. It must have something to do with the fact that it's AM
minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it
worked
great.
I did a paste special and pasted that formula into another cell and
it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that
then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Nick .... or even parentheses ;-))))) <vbg

--
Regards

Roger Govier


"Nick Hodge" wrote in message
...
Roger...they're parenthesis, not brackets ;-)))))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Roger Govier" wrote in message
...
Hi Karen

One way to deal with times crossing the midnight barrier, is to use
=MOD(G11-C11,1) instead of plain G11-C11

Therefore modifying Nick's formula to
=(((MOD(G11-C11,1))*60)*24)&" min."

or even removing Nick's penchant for brackets ( no offence Nick <bg)
=MOD(G11-C11,1)*60*24 &" min."
will give you the correct answer of 47 min

--
Regards

Roger Govier


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I
used the
paste special on another cell and it is not returning the correct
value. The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the
result is
-1393 mins. It must have something to do with the fact that it's AM
minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and
it worked
great.
I did a paste special and pasted that formula into another cell
and it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I
get a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after
that then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of
0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

I don't understand why I'm getting different results when I do a paste
special. When I paste the formula into certain cells, the result is fine.
Then when I paste the formula into another cell, I'm getting a result of
16.9999999999999 and Excel will not allow me to decrease the decimals places.
Why is this happening and how can I get a result of 17 min.?

Cell F18 = 10:17 AM
Cell D18 = 10:00 AM
Cell H18 = The formula is: =(((F18-D18)*60)*24)&" min."
Result in H18 = 16.9999999999999

HELP!


"Roger Govier" wrote:

Hi Karen

One way to deal with times crossing the midnight barrier, is to use
=MOD(G11-C11,1) instead of plain G11-C11

Therefore modifying Nick's formula to
=(((MOD(G11-C11,1))*60)*24)&" min."

or even removing Nick's penchant for brackets ( no offence Nick <bg)
=MOD(G11-C11,1)*60*24 &" min."
will give you the correct answer of 47 min

--
Regards

Roger Govier


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I
used the
paste special on another cell and it is not returning the correct
value. The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result
is
-1393 mins. It must have something to do with the fact that it's AM
minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it
worked
great.
I did a paste special and pasted that formula into another cell and
it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get
a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that
then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

You are seeing the effect of trying to use binary representation of
fractional numbers, and suffering rounding errors in so doing.

Just as you would get problems if you tried to represent 1/3 in a fixed
number of places of decimals, and then add 3 of those together and not get
1, you'll get the same with binary representation of numbers other than 0.5,
0.25, 0.125, etc. and their multiples. 10:00 AM is represented as 10/24,
which can't be represented exactly in either decimal or binary, and
similarly with 10:17. You may need to round your result appropriately.
--
David Biddulph

"Karen" wrote in message
...

I don't understand why I'm getting different results when I do a paste
special. When I paste the formula into certain cells, the result is fine.
Then when I paste the formula into another cell, I'm getting a result of
16.9999999999999 and Excel will not allow me to decrease the decimals
places.
Why is this happening and how can I get a result of 17 min.?

Cell F18 = 10:17 AM
Cell D18 = 10:00 AM
Cell H18 = The formula is: =(((F18-D18)*60)*24)&" min."
Result in H18 = 16.9999999999999

HELP!


"Roger Govier" wrote:

Hi Karen

One way to deal with times crossing the midnight barrier, is to use
=MOD(G11-C11,1) instead of plain G11-C11

Therefore modifying Nick's formula to
=(((MOD(G11-C11,1))*60)*24)&" min."

or even removing Nick's penchant for brackets ( no offence Nick <bg)
=MOD(G11-C11,1)*60*24 &" min."
will give you the correct answer of 47 min

--
Regards

Roger Govier


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I
used the
paste special on another cell and it is not returning the correct
value. The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result
is
-1393 mins. It must have something to do with the fact that it's AM
minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it
worked
great.
I did a paste special and pasted that formula into another cell and
it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get
a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that
then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

What do you mean by "Excel will not allow me to decrease the decimals
places"? What cell format do you have? What response do you get from Excel
when you try to change the cell format? Can't you format it to number with
zero decimal places?
--
David Biddulph

"Karen" wrote in message
...

I don't understand why I'm getting different results when I do a paste
special. When I paste the formula into certain cells, the result is fine.
Then when I paste the formula into another cell, I'm getting a result of
16.9999999999999 and Excel will not allow me to decrease the decimals
places.
Why is this happening and how can I get a result of 17 min.?

Cell F18 = 10:17 AM
Cell D18 = 10:00 AM
Cell H18 = The formula is: =(((F18-D18)*60)*24)&" min."
Result in H18 = 16.9999999999999

HELP!

Time in Minutes
 

Not just binary:
1/24 = 1/3 * 1/2^3
1/24/60 = 1/3^2 *(1/5 * 1/2^5)
So in general neither hours nor minutes could be exactly represented as
decimal fractions either, and would therefore still be subject to rounding
errors in calculations.

Jerry

"David Biddulph" wrote:

You are seeing the effect of trying to use binary representation of
fractional numbers, and suffering rounding errors in so doing.

Just as you would get problems if you tried to represent 1/3 in a fixed
number of places of decimals, and then add 3 of those together and not get
1, you'll get the same with binary representation of numbers other than 0.5,
0.25, 0.125, etc. and their multiples. 10:00 AM is represented as 10/24,
which can't be represented exactly in either decimal or binary, and
similarly with 10:17. You may need to round your result appropriately.
--
David Biddulph

"Karen" wrote in message
...

I don't understand why I'm getting different results when I do a paste
special. When I paste the formula into certain cells, the result is fine.
Then when I paste the formula into another cell, I'm getting a result of
16.9999999999999 and Excel will not allow me to decrease the decimals
places.
Why is this happening and how can I get a result of 17 min.?

Cell F18 = 10:17 AM
Cell D18 = 10:00 AM
Cell H18 = The formula is: =(((F18-D18)*60)*24)&" min."
Result in H18 = 16.9999999999999

HELP!


"Roger Govier" wrote:

Hi Karen

One way to deal with times crossing the midnight barrier, is to use
=MOD(G11-C11,1) instead of plain G11-C11

Therefore modifying Nick's formula to
=(((MOD(G11-C11,1))*60)*24)&" min."

or even removing Nick's penchant for brackets ( no offence Nick <bg)
=MOD(G11-C11,1)*60*24 &" min."
will give you the correct answer of 47 min

--
Regards

Roger Govier


"Karen" wrote in message
...
Now it works in that cell - I don't know what I'm doing wrong, but I
used the
paste special on another cell and it is not returning the correct
value. The
cell references in the formula seem fine.

This is what I have:
Cell G11 = 12:44 AM
Cell C11 = 11:57 PM
The formula in cell I11 is =(((G11-C11)*60)*24)&" min." and the result
is
-1393 mins. It must have something to do with the fact that it's AM
minus
PM. How can this be fixed?

Thank you VERY much for all your help, Karen



"Nick Hodge" wrote:

Karen

Worked for me...are you sure your formula reads as below

=(((F5-D5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
Thank you for your help - I used =(((G5-C5)*60)*24)&" mins" and it
worked
great.
I did a paste special and pasted that formula into another cell and
it's
not
working as planned.

I have:
Cell F5 = 1:28 AM
Cell D5 = 1:12 AM
I want the difference of F5-D5, which is 16 minutes. How do I get
a
return
of "16 mins"?

Thank you, Karen

"Nick Hodge" wrote:

Karen

=(((G5-C5)*60)*24)

Will give you 43. If you are using the number in a calc after that
then
custom format (FormatCells...Custom) and enter 0 "mins"

If you are not using the result then add to the formula

=(((G5-C5)*60)*24)&" mins"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Karen" wrote in message
...
This is the scenario:

Cell G5 = 1: 39 AM
Cell C5 = 12:56 AM
Cell I5 = (Formula) =G5-C5

The result is 0:43
Is there any way to return a result of 43 min. instead of 0:43?

Any help would be greatly appreciated - Thank you, Karen

Time in Minutes
 

Thank you for your response - When I click on the cell with the result
16.9999999999999, and then click on the "Decrease Decimal" icon, the decimal
will not decrease. Then whild clicked in that same cell, I go to Format
Cells and the format is: Time 13:30. Also, when I format it to a number
with zero decimal places, it's still 16.9999999999999 - I thought that maybe
it was cell F18 & D18 that was causing the problem, so I clicked in each cell
and did Edit Clear All and re-entered the time. I'm still getting the
same result with 13 decimal places.
What else can I do? My boss is on my butt about this.
HELP!

"David Biddulph" wrote:

What do you mean by "Excel will not allow me to decrease the decimals
places"? What cell format do you have? What response do you get from Excel
when you try to change the cell format? Can't you format it to number with
zero decimal places?
--
David Biddulph

"Karen" wrote in message
...

I don't understand why I'm getting different results when I do a paste
special. When I paste the formula into certain cells, the result is fine.
Then when I paste the formula into another cell, I'm getting a result of
16.9999999999999 and Excel will not allow me to decrease the decimals
places.
Why is this happening and how can I get a result of 17 min.?

Cell F18 = 10:17 AM
Cell D18 = 10:00 AM
Cell H18 = The formula is: =(((F18-D18)*60)*24)&" min."
Result in H18 = 16.9999999999999

HELP!

Time in Minutes
 

Yes, I see the problem now. I was reading the bit where you said "Result in
H18 = 16.9999999999999", but hadn't read that in conjuction with where you
showed that you were concatenating with a text string in that cell. Because
you're converting it into a text string the cell formatting isn't being
applied. You need
=TEXT(((F18-D18)*60)*24,"0")&" min."
--
David Biddulph

"Karen" wrote in message
...
Thank you for your response - When I click on the cell with the result
16.9999999999999, and then click on the "Decrease Decimal" icon, the
decimal
will not decrease. Then whild clicked in that same cell, I go to Format
Cells and the format is: Time 13:30. Also, when I format it to a number
with zero decimal places, it's still 16.9999999999999 - I thought that
maybe
it was cell F18 & D18 that was causing the problem, so I clicked in each
cell
and did Edit Clear All and re-entered the time. I'm still getting the
same result with 13 decimal places.
What else can I do? My boss is on my butt about this.
HELP!

"David Biddulph" wrote:

What do you mean by "Excel will not allow me to decrease the decimals
places"? What cell format do you have? What response do you get from
Excel
when you try to change the cell format? Can't you format it to number
with
zero decimal places?
--
David Biddulph

"Karen" wrote in message
...

I don't understand why I'm getting different results when I do a paste
special. When I paste the formula into certain cells, the result is
fine.
Then when I paste the formula into another cell, I'm getting a result
of
16.9999999999999 and Excel will not allow me to decrease the decimals
places.
Why is this happening and how can I get a result of 17 min.?

Cell F18 = 10:17 AM
Cell D18 = 10:00 AM
Cell H18 = The formula is: =(((F18-D18)*60)*24)&" min."
Result in H18 = 16.9999999999999

HELP!