caculating time

[strathy]

Is there a way in a spreadsheet to add a list of time and have it give
you a total of number of hours and minutes? Thanks! Donna
For example:
12:33pm to
1:48pm
Total: (hours&minutes)
11:30am to
12:44pm
Totalhours&minutes)
TOTAL(of Total)

 

[JE McGimpsey]

Is there a way in a spreadsheet to add a list of time and have it give
you a total of number of hours and minutes? Thanks! Donna
For example:
12:33pm to
1:48pm
Total: (hours&minutes)
11:30am to
12:44pm
Totalhours&minutes)
TOTAL(of Total)

Since times are stored in XL as fractional days (e.g., 6:00 = 0.25), you
can just do regular math on them:

A1: 12:33 pm
A2: 1:48 pm

A4: =A2-A1 ===> 1:15

when you format A4 with Format/Cells/Number/Custom [h]:mm

if your times will span midnight, you'll need to account for the "later"
time (e.g., 3:00 am = 0.125) being smaller than the "earlier" time
(e.g., 9:00 pm = 0.875).

You can use the fact that XL treats boolean TRUE/FALSE values as 1/0,
respectively:

A4: =A2-A1 + (A2<A1)

or the somewhat more obscure, but equivalent

A4: =MOD(A2-A1,1)

 

[strathy]

Thanks Jim, for you help. I will try to make it work! Donna

 

[strathy]

Hi Jim,
I understand Format/Cells/Number/Custom [h]:mm but not sure how to
format the specific cell to perform the function A4.
Thanks, Donna

 

[JE McGimpsey]

I understand Format/Cells/Number/Custom [h]:mm but not sure how to
format the specific cell to perform the function A4.

The A4 in my example was just the cell reference that I put the formula
in for my example. The actual formula is simply

= A2 - A1

where cell A2 contains the end time and cell A1 contains the start time.

 

[strathy]

How do you do the actual caculation of A2-A1? When using the formula
bar i see sum & count in the sub menu but can't find a subtraction
function. Or should i be using the auto sum button or the calculator?
Thanks

 

[JE McGimpsey]

How do you do the actual caculation of A2-A1? When using the formula
bar i see sum & count in the sub menu but can't find a subtraction
function. Or should i be using the auto sum button or the calculator?

Use the subtraction operator (-), i.e., type a hyphen:

= A2 - A1

just as I've been posting it.

There's no need to use functions in this case, nor in many others. For
instance, the function

=SUM(A1:A2)

is generally more efficient using the addition operator (+):

= A1 + A2

 

[strathy]

When i use the subtraction operator, in my case = C6 - C5, i get
#VALUE!.

 

[JE McGimpsey]

When i use the subtraction operator, in my case = C6 - C5, i get
#VALUE!.

Then one or both of the values in C5 & C6 are probably Text rather than
numbers. Make sure the cells are formatted as numbers, then reenter them.

 

[strathy]

i formatted the cell:
A7 with Format/Cells/Number/Custom [h]:mm

 

[J Laroche]

Donna

When i use the subtraction operator, in my case = C6 - C5, i get
#VALUE!.
JE
Then one or both of the values in C5 & C6 are probably Text rather than
numbers. Make sure the cells are formatted as numbers, then reenter them.
Donna
i formatted the cell:
A7 with Format/Cells/Number/Custom [h]:mm

The cells to which to apply a numeric format are the ones providing the
operands for the cell where the error appears. So if the error appears in
A7, where the formula is =C6-C5, then C5 and C6 must contain a numeric value
and be formatted with a numeric format to produce a valid result. You can
modify the display of the value in A7 by applying to it its own format, that
can be different from the format of two others cells.

Obviously, you can only subtract (and add, multiply, etc) numbers, not text.
Text may appear as a number though, which can make troubleshooting
difficult. And a cell formatted with a numeric format may still not contain
a number suitable for mathematical operations if an unacceptable character
(semi-colon, letter, etc.) is part of its content. By default all cells are
formatted as general, which means that Excel smartly decides which format to
use based on the original content of the cell.

Donna, do yourself a favor and please get some basic Excel instruction, with
a book, a video or an online tutorial. You'll appreciate the software much
more when you know its possibilities, even the basic ones.

JL
Mac OS X 10.3.9, Office v.X 10.1.6