Calculating Time
 

Believe me, I've searched Help, but I'm not finding my answer. In A2 I have
a time, formatted as time, which is 8:30 AM. In B7 I have a time, formatted
as time, which is 8:00 AM. G7 is formatted h:mm, and I have my formula
there, which is =SUM($A$2-B7), which is working properly. It gives me 0:30.
Now's the problem. In C7 I have 30 (meaning 30 minutes). In D7, I have 120
(meaning 120 minutes). I don't know how to format C7 and D7 to minutes. I
want to also extend the formula in G7 to not only calculate the difference in
time between A2 and B7, but to add the minutes in C7 and subtract the minutes
in D7. The formula in G7 should give -0:60.

Can anyone help? Connie

Calculating Time
 

Excel stores times as fractions of a day.
So to get minutes, divide by the number of minutes in a day:

=A1/24/60
will give you A1 as Excel minutes. Of course
=A1/1440
will do as well.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
Believe me, I've searched Help, but I'm not finding my answer. In A2 I
have
a time, formatted as time, which is 8:30 AM. In B7 I have a time,
formatted
as time, which is 8:00 AM. G7 is formatted h:mm, and I have my formula
there, which is =SUM($A$2-B7), which is working properly. It gives me
0:30.
Now's the problem. In C7 I have 30 (meaning 30 minutes). In D7, I have
120
(meaning 120 minutes). I don't know how to format C7 and D7 to minutes.
I
want to also extend the formula in G7 to not only calculate the difference
in
time between A2 and B7, but to add the minutes in C7 and subtract the
minutes
in D7. The formula in G7 should give -0:60.

Can anyone help? Connie

Calculating Time
 

Firstly, you don't need the SUM function in =SUM($A$2-B7). If you want
$A$2-B7, use =$A$2-B7. You haven't told Excel anything to which you want
that summing.

Secondly, if you want to convert a number of minutes to an Excel time,
divide by 1440 (or divide by 60 and then by 24, or vice versa).
--
David Biddulph

Connie Martin wrote:
Believe me, I've searched Help, but I'm not finding my answer. In A2
I have a time, formatted as time, which is 8:30 AM. In B7 I have a
time, formatted as time, which is 8:00 AM. G7 is formatted h:mm, and
I have my formula there, which is =SUM($A$2-B7), which is working
properly. It gives me 0:30. Now's the problem. In C7 I have 30
(meaning 30 minutes). In D7, I have 120 (meaning 120 minutes). I
don't know how to format C7 and D7 to minutes. I want to also extend
the formula in G7 to not only calculate the difference in time
between A2 and B7, but to add the minutes in C7 and subtract the
minutes in D7. The formula in G7 should give -0:60.

Can anyone help? Connie

Calculating Time
 

So, I have to put in an extra column to convert my numbers to Excel time?
I've tried that and then used those columns to calculate by but I'm getting
##################### as the answer. Connie

"Niek Otten" wrote:

Excel stores times as fractions of a day.
So to get minutes, divide by the number of minutes in a day:

=A1/24/60
will give you A1 as Excel minutes. Of course
=A1/1440
will do as well.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
Believe me, I've searched Help, but I'm not finding my answer. In A2 I
have
a time, formatted as time, which is 8:30 AM. In B7 I have a time,
formatted
as time, which is 8:00 AM. G7 is formatted h:mm, and I have my formula
there, which is =SUM($A$2-B7), which is working properly. It gives me
0:30.
Now's the problem. In C7 I have 30 (meaning 30 minutes). In D7, I have
120
(meaning 120 minutes). I don't know how to format C7 and D7 to minutes.
I
want to also extend the formula in G7 to not only calculate the difference
in
time between A2 and B7, but to add the minutes in C7 and subtract the
minutes
in D7. The formula in G7 should give -0:60.

Can anyone help? Connie

Calculating Time
 

Thank you for responding. However, I will say the same thing I said to Niek:
"So, I have to put in an extra column to convert my numbers to Excel time?
I've tried that and then used those columns to calculate by but I'm getting
##################### as the answer." Connie

"David Biddulph" wrote:

Firstly, you don't need the SUM function in =SUM($A$2-B7). If you want
$A$2-B7, use =$A$2-B7. You haven't told Excel anything to which you want
that summing.

Secondly, if you want to convert a number of minutes to an Excel time,
divide by 1440 (or divide by 60 and then by 24, or vice versa).
--
David Biddulph

Connie Martin wrote:
Believe me, I've searched Help, but I'm not finding my answer. In A2
I have a time, formatted as time, which is 8:30 AM. In B7 I have a
time, formatted as time, which is 8:00 AM. G7 is formatted h:mm, and
I have my formula there, which is =SUM($A$2-B7), which is working
properly. It gives me 0:30. Now's the problem. In C7 I have 30
(meaning 30 minutes). In D7, I have 120 (meaning 120 minutes). I
don't know how to format C7 and D7 to minutes. I want to also extend
the formula in G7 to not only calculate the difference in time
between A2 and B7, but to add the minutes in C7 and subtract the
minutes in D7. The formula in G7 should give -0:60.

Can anyone help? Connie

Calculating Time
 

As extra-sensory perception isn't necessarily a qualification for those who
read this group, you might perhaps wish to tell us what formulae you are
using, and what values are in the cells that are referred to in those
formulae?
--
David Biddulph

Connie Martin wrote:
Thank you for responding. However, I will say the same thing I said
to Niek: "So, I have to put in an extra column to convert my numbers
to Excel time? I've tried that and then used those columns to
calculate by but I'm getting ##################### as the answer."
Connie

"David Biddulph" wrote:

Firstly, you don't need the SUM function in =SUM($A$2-B7). If you
want $A$2-B7, use =$A$2-B7. You haven't told Excel anything to
which you want that summing.

Secondly, if you want to convert a number of minutes to an Excel
time, divide by 1440 (or divide by 60 and then by 24, or vice versa).
--
David Biddulph

Connie Martin wrote:
Believe me, I've searched Help, but I'm not finding my answer. In
A2 I have a time, formatted as time, which is 8:30 AM. In B7 I
have a time, formatted as time, which is 8:00 AM. G7 is formatted
h:mm, and I have my formula there, which is =SUM($A$2-B7), which is
working properly. It gives me 0:30. Now's the problem. In C7 I
have 30 (meaning 30 minutes). In D7, I have 120 (meaning 120
minutes). I don't know how to format C7 and D7 to minutes. I want
to also extend the formula in G7 to not only calculate the
difference in time between A2 and B7, but to add the minutes in C7
and subtract the minutes in D7. The formula in G7 should give
-0:60.

Can anyone help? Connie

Calculating Time
 

Excel's string of #s is trying to tell you that you have a negative time,
which Excel does not support.

You may be able to work around this with something like:
="-"&text(abs(yourcaclulation),"h:mm")

Regards,
Fred.

"Connie Martin" wrote in message
...
So, I have to put in an extra column to convert my numbers to Excel time?
I've tried that and then used those columns to calculate by but I'm
getting
##################### as the answer. Connie

"Niek Otten" wrote:

Excel stores times as fractions of a day.
So to get minutes, divide by the number of minutes in a day:

=A1/24/60
will give you A1 as Excel minutes. Of course
=A1/1440
will do as well.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
Believe me, I've searched Help, but I'm not finding my answer. In A2 I
have
a time, formatted as time, which is 8:30 AM. In B7 I have a time,
formatted
as time, which is 8:00 AM. G7 is formatted h:mm, and I have my formula
there, which is =SUM($A$2-B7), which is working properly. It gives me
0:30.
Now's the problem. In C7 I have 30 (meaning 30 minutes). In D7, I
have
120
(meaning 120 minutes). I don't know how to format C7 and D7 to
minutes.
I
want to also extend the formula in G7 to not only calculate the
difference
in
time between A2 and B7, but to add the minutes in C7 and subtract the
minutes
in D7. The formula in G7 should give -0:60.

Can anyone help? Connie

Calculating Time
 

Connie,

As Fred pointed out, the ###### is the result of a formula that evaluates as
negative time. You can 'correct' this by using the 1904 date option in
excel, under Tools, Options, Calculations (in Excel 2003). If you tick the
1904 box, you will be able to the view the negative time; however, this will
impact other sheets you may be using, so use caution.

The formula you have laid out would evaluate to -1:00:00 AM, which is
functionally the same as -0:60.

Given Excels limited functionality in negative times, I suggest that you add
something like this in column H:
=IF(G7<0,TEXT(G7*-1,"hh:mm:ss")&" Neg",G7)

This way you can avoid the 1904 date system and have an positive or negative
time value.

This won't address all the issues, but it will be a start.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Connie Martin" wrote:

Thank you for responding. However, I will say the same thing I said to Niek:
"So, I have to put in an extra column to convert my numbers to Excel time?
I've tried that and then used those columns to calculate by but I'm getting
##################### as the answer." Connie

"David Biddulph" wrote:

Firstly, you don't need the SUM function in =SUM($A$2-B7). If you want
$A$2-B7, use =$A$2-B7. You haven't told Excel anything to which you want
that summing.

Secondly, if you want to convert a number of minutes to an Excel time,
divide by 1440 (or divide by 60 and then by 24, or vice versa).
--
David Biddulph

Connie Martin wrote:
Believe me, I've searched Help, but I'm not finding my answer. In A2
I have a time, formatted as time, which is 8:30 AM. In B7 I have a
time, formatted as time, which is 8:00 AM. G7 is formatted h:mm, and
I have my formula there, which is =SUM($A$2-B7), which is working
properly. It gives me 0:30. Now's the problem. In C7 I have 30
(meaning 30 minutes). In D7, I have 120 (meaning 120 minutes). I
don't know how to format C7 and D7 to minutes. I want to also extend
the formula in G7 to not only calculate the difference in time
between A2 and B7, but to add the minutes in C7 and subtract the
minutes in D7. The formula in G7 should give -0:60.

Can anyone help? Connie