subtracting times using 24 hour clock

[Guest]

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?

 

[Harald Staff]

Hi Andrew

=B1-A1+(B1<A1)

HTH. Best wishes Harald

 

[Guest]

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?

 

[Dewi]

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?

 

[Harald Staff]

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

 

[Steve Smallman]

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

 

[David McRitchie]

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