I am trying to put together a fool proof spreadsheet in which it will
automatically compute time. For example; =SUM((B3-B2)) This would be (B3)
06:15 - 15:15 (B2) the result would be 9:00 hours, however when trying to
compute 23:15 - 02:15, using this formula it will not work. I would like to
find out how this can be corrected to compute the right time used. I have
tried the formula =SUM((B3-B2)*24)); this does not help either or even
replacing the *24 with *12. Your assistance would be greatly appreaciated.

Hi

If B2 is 15:15 and B3 is 6:15 , then isn't it B2-B3 ?

Also, you don't need the SUM( ) thing for this. Simply
=B2-B3

When end time pass midnight then the end time is smaller than start time
and you get trouble -which I believe is what you are asking for here. This
is easiest corrected this way:
=B2-B3+(B2<B3)

Replace B2 with whatever end time address you have and B3 with whatever
start time address.

HTH. Best wishes Harald


"bil391" skrev i melding
...
I am trying to put together a fool proof spreadsheet in which it will
automatically compute time. For example; =SUM((B3-B2)) This would be (B3)
06:15 - 15:15 (B2) the result would be 9:00 hours, however when trying to
compute 23:15 - 02:15, using this formula it will not work. I would like
to
find out how this can be corrected to compute the right time used. I have
tried the formula =SUM((B3-B2)*24)); this does not help either or even
replacing the *24 with *12. Your assistance would be greatly appreaciated.

I just wanted to thank you very much, your assistance has solved the minor
problem, except I added the *12 at the end of the formula and it worked
flawlessly after that.

"Harald Staff" wrote:

Hi

If B2 is 15:15 and B3 is 6:15 , then isn't it B2-B3 ?

Also, you don't need the SUM( ) thing for this. Simply
=B2-B3

When end time pass midnight then the end time is smaller than start time
and you get trouble -which I believe is what you are asking for here. This
is easiest corrected this way:
=B2-B3+(B2<B3)

Replace B2 with whatever end time address you have and B3 with whatever
start time address.

HTH. Best wishes Harald


"bil391" skrev i melding
...
I am trying to put together a fool proof spreadsheet in which it will
automatically compute time. For example; =SUM((B3-B2)) This would be (B3)
06:15 - 15:15 (B2) the result would be 9:00 hours, however when trying to
compute 23:15 - 02:15, using this formula it will not work. I would like
to
find out how this can be corrected to compute the right time used. I have
tried the formula =SUM((B3-B2)*24)); this does not help either or even
replacing the *24 with *12. Your assistance would be greatly appreaciated.

bil

Visit Chip Pearson's site and check out the pages on doing aritmetic with
times and dates.

Lots of info and formulas.

http://www.cpearson.com/excel/datetime.htm#AddingTimes


Gord Dibben Excel MVP

On Fri, 28 Jan 2005 15:03:01 -0800, bil391
wrote:

I am trying to put together a fool proof spreadsheet in which it will
automatically compute time. For example; =SUM((B3-B2)) This would be (B3)
06:15 - 15:15 (B2) the result would be 9:00 hours, however when trying to
compute 23:15 - 02:15, using this formula it will not work. I would like to
find out how this can be corrected to compute the right time used. I have
tried the formula =SUM((B3-B2)*24)); this does not help either or even
replacing the *24 with *12. Your assistance would be greatly appreaciated.