Time calculations over 24hrs

Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian

On Thu, 6 May 2010 11:32:35 +0100, Ian R wrote:

Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian


For up to 24 hrs:

=B1-A1+(B1=A1)

Format result as h:mm

For more than 24 hours, you will have to include the date also, and format the
result as [h]:mm

--ron

Hi Ian,

That's because Excel sees 00:20 (without a date) as being less than 19:50,
and Excel doesn't recognise the viability of time-travel. i.e. you can't
have a negative expression of time.

Actually, I'm a little confused by your question; you state that you are
subtracting B1 from A1, but show your formula as B1-A1, which is the other
way around. For this I will assume you have start time in A1 and end time
in B1, so your formula would have been correct rather than your description.

To get around your problem you have to let Excel know that 00:20 is actually
the next day, which you can do by adding 1 to it when B1 is less than A1.
This will achieve that:

=B1+(B1A1)-A1


HTH
Steve D.


"Ian R" wrote in message
m...
Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian

Comparing Ron's answer to mine raises another question, how do you want
Excel to deal with A1=B1?




"Steve Dunn" wrote in message
...
Hi Ian,

That's because Excel sees 00:20 (without a date) as being less than 19:50,
and Excel doesn't recognise the viability of time-travel. i.e. you can't
have a negative expression of time.

Actually, I'm a little confused by your question; you state that you are
subtracting B1 from A1, but show your formula as B1-A1, which is the other
way around. For this I will assume you have start time in A1 and end time
in B1, so your formula would have been correct rather than your
description.

To get around your problem you have to let Excel know that 00:20 is
actually the next day, which you can do by adding 1 to it when B1 is less
than A1. This will achieve that:

=B1+(B1A1)-A1


HTH
Steve D.


"Ian R" wrote in message
m...
Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian

Use

=MOD(B1-A1,1)

--

HTH

Bob

"Ian R" wrote in message
m...
Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian

In article ,
says...
Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian


Thanks to you all for taking the time to reply.

Its working perfectly now - just what I needed!

Much appreciated.

Ian I^)