Prev: Formula help please
Next: formula to return value from a matrix - I know hor and vert values
From: Ian R on 6 May 2010 06:32
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
From: p45cal on 6 May 2010 06:49

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



I
a
n

R
;
7
1
7
8
9
8

W
r
o
t
e
:


>
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

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=201009

http://www.thecodecage.com/forumz

From: Ron Rosenfeld on 6 May 2010 06:53
On Thu, 6 May 2010 11:32:35 +0100, Ian R <nospam(a)nopsam.com> 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
From: Steve Dunn on 6 May 2010 06:59
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.


"Ian R" <nospam(a)nopsam.com> wrote in message
news:MPG.264caaf851b12059989682(a)news.btinternet.com...
> 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

From: Steve Dunn on 6 May 2010 07:06
Comparing Ron's answer to mine raises another question, how do you want
Excel to deal with A1=B1?




"Steve Dunn" <stunn(a)sky.com> wrote in message
news:uip%23HsQ7KHA.1888(a)TK2MSFTNGP05.phx.gbl...
> 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.
>
>
> "Ian R" <nospam(a)nopsam.com> wrote in message
> news:MPG.264caaf851b12059989682(a)news.btinternet.com...
>> 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
>

 |  Next  |  Last
Pages: 1 2
Prev: Formula help please
Next: formula to return value from a matrix - I know hor and vert values