Time calculations over 24hrs

[Ian R]

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

 

[p45cal]

If the result is never over 24 hours try:
=IF(B1>A1,B1-A1,B1+1-A1)

 

[Ron Rosenfeld]

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

 

[Steve Dunn]

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+(B1<A1)-A1


HTH
Steve D.

 

[Steve Dunn]

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

 

[Bob Phillips]

Use

=MOD(B1-A1,1)

 

[Bob Phillips]

Are you sure your clock is right, yours was the last to hit this thread, but
is timed the earliest!

 

[p45cal]

Yeah. I'm posting at thecodecage.com. I know it's been having som
server problems lately.

Are you sure your clock is right, yours was the last to hit this thread
but

is timed the earliest!

 

[Ian R]

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^)