subtracting times using 24 hour clock
 

I want to calculate a period of time in 24 hours to be able to work out the
running speed of a machine. This is ok using say 18:00 to 21:00 the resulting
time can be shown as a decimal & the machine output divided by the result,
however how do I calculate the difference over the midnight period ie say
11:30 to 02:00?
--
Andrew P.

Hi Andrew

=B1-A1+(B1<A1)

HTH. Best wishes Harald

"andrew pronto" .(donotspam) skrev i melding
...
I want to calculate a period of time in 24 hours to be able to work out
the
running speed of a machine. This is ok using say 18:00 to 21:00 the
resulting
time can be shown as a decimal & the machine output divided by the result,
however how do I calculate the difference over the midnight period ie say
11:30 to 02:00?
--
Andrew P.

Hi Harald
Thanks for info, however ie: say A1=18:00 shown by excel as 0.75, B1=06:00
shown as 0.25 if I apply your formulae I get 0.5?


"Harald Staff" wrote:

Hi Andrew

=B1-A1+(B1<A1)

HTH. Best wishes Harald

"andrew pronto" .(donotspam) skrev i melding
...
I want to calculate a period of time in 24 hours to be able to work out
the
running speed of a machine. This is ok using say 18:00 to 21:00 the
resulting
time can be shown as a decimal & the machine output divided by the result,
however how do I calculate the difference over the midnight period ie say
11:30 to 02:00?
--
Andrew P.

On Tue, 23 Nov 2004 09:53:12 -0800, "andrew pronto"
.(donotspam) uttered:

Hi Harald
Thanks for info, however ie: say A1=18:00 shown by excel as 0.75, B1=06:00
shown as 0.25 if I apply your formulae I get 0.5?


0.5 (1/2) of 24hrs is 12hrs?


--
Dewi,

(remove spin for email)

Yes. 1 is a day; 24 hours. So 0.5 is half a day; 12 hours. 0.75 is three
quarters of a day; 18 hours. Simply format the cells as time.

HTH. Best wishes Harald

"andrew pronto" .(donotspam) skrev i melding
...
Hi Harald
Thanks for info, however ie: say A1=18:00 shown by excel as 0.75, B1=06:00
shown as 0.25 if I apply your formulae I get 0.5?


"Harald Staff" wrote:

Hi Andrew

=B1-A1+(B1<A1)

HTH. Best wishes Harald

"andrew pronto" .(donotspam) skrev i melding
...
I want to calculate a period of time in 24 hours to be able to work
out
the
running speed of a machine. This is ok using say 18:00 to 21:00 the
resulting
time can be shown as a decimal & the machine output divided by the
result,
however how do I calculate the difference over the midnight period ie
say
11:30 to 02:00?
--
Andrew P.

Start time in column A, end time in column b, calc in column C
23:30 in A2, 02:00 in B2 formula in C2 =if(B2<A2, b2+1-a2, b2-a2) assumes
all tests less than 24 hours
alternatively, enter 01/01/01 23:30 in A2 and 02/01/01 02:00 in the cells
(using appropriate dates and formats)

the problem is that if you subtract 12:00 from 06:00 (i.e. start at noon and
finish at 6am next day) you are subtracting .5 from .25 in a date format,
resulting in a negative, and excel does not recognise dates as being a
negative.

I suggest you read up on the way Excel handles dates and times as the task
you are attempting can be complex.

Try Chip Pearson's site, an excellent reference

Steve

"andrew pronto" .(donotspam) wrote in message
...
I want to calculate a period of time in 24 hours to be able to work out the
running speed of a machine. This is ok using say 18:00 to 21:00 the
resulting
time can be shown as a decimal & the machine output divided by the result,
however how do I calculate the difference over the midnight period ie say
11:30 to 02:00?
--
Andrew P.

Hi Andrew,
You would find formulas like the following on Chip's site and on my site,
I got it from Stephen Bullen (without explanation), glad I recognized
what it was. Arithmetically it is the same as Steve's.
C2: =B2-A2 + (B2<A2)
First you see the regular subtraction, but if B2 is less than A2
it will add 1 from the logical B2<A2 as being True; otherwise, it
adds 0 for false. Dates and times are measured in units of days so
adding 1 is same as adding 24 hours.

Chip's page on Date and Time
http://www.cpearson.com/excel/datetime.htm

My page on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Steve Smallman" wrote in message ...
Start time in column A, end time in column b, calc in column C
23:30 in A2, 02:00 in B2 formula in C2 =if(B2<A2, b2+1-a2, b2-a2) assumes
all tests less than 24 hours