time subtraction problem

Hi,

Lets say your start time is in cell A1 and end time is in B1, use the
formula

=IF(B1<A1,B1+1-A1,B1-A1)

and format the cell in the time format you wish.

Regards

Govind.

a wrote:
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
> 1:50
> excel gives me gibberish.
>
> thnks.

<a> wrote in message news:[email protected]...

> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
formula
> down the whole column.

and mine doesn't?

=MOD(B1-A1,1)

--
HTH

Bob Phillips

<a> wrote in message news:[email protected]...
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
1:50
> excel gives me gibberish.
>
> thnks.
>

thanks.

both formulae worked,
but I would prefer the IF statement even though its abit longer.
since it applies to both cases of A1 > B1 or B1 > A1, i can drag the formula
down the whole column.

BTW, what does (time + 1) do to the smaller time?

thnks.

You can also copy Bob's formula down the column. It will work with both
cases.

--
Regards
Roger Govier
<a> wrote in message news:[email protected]...
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

Hi,

Thanks for the feedback. The +1 adds one day to the smaller time.

Govind.

a wrote:
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

<a> wrote in message news:[email protected]...

> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
formula
> down the whole column.

and mine doesn't?

Hi,

Lets say your start time is in cell A1 and end time is in B1, use the
formula

=IF(B1<A1,B1+1-A1,B1-A1)

and format the cell in the time format you wish.

Regards

Govind.

a wrote:
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
> 1:50
> excel gives me gibberish.
>
> thnks.

You can also copy Bob's formula down the column. It will work with both
cases.

--
Regards
Roger Govier
<a> wrote in message news:[email protected]...
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

=MOD(B1-A1,1)

--
HTH

Bob Phillips

<a> wrote in message news:[email protected]...
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
1:50
> excel gives me gibberish.
>
> thnks.
>

Hi,

Thanks for the feedback. The +1 adds one day to the smaller time.

Govind.

a wrote:
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

thanks.

both formulae worked,
but I would prefer the IF statement even though its abit longer.
since it applies to both cases of A1 > B1 or B1 > A1, i can drag the formula
down the whole column.

BTW, what does (time + 1) do to the smaller time?

thnks.

<a> wrote in message news:[email protected]...

> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
formula
> down the whole column.

and mine doesn't?

You can also copy Bob's formula down the column. It will work with both
cases.

--
Regards
Roger Govier
<a> wrote in message news:[email protected]...
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

Hi,

Thanks for the feedback. The +1 adds one day to the smaller time.

Govind.

a wrote:
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

thanks.

both formulae worked,
but I would prefer the IF statement even though its abit longer.
since it applies to both cases of A1 > B1 or B1 > A1, i can drag the formula
down the whole column.

BTW, what does (time + 1) do to the smaller time?

thnks.

=MOD(B1-A1,1)

--
HTH

Bob Phillips

<a> wrote in message news:[email protected]...
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
1:50
> excel gives me gibberish.
>
> thnks.
>

Hi,

Lets say your start time is in cell A1 and end time is in B1, use the
formula

=IF(B1<A1,B1+1-A1,B1-A1)

and format the cell in the time format you wish.

Regards

Govind.

a wrote:
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
> 1:50
> excel gives me gibberish.
>
> thnks.

Hi,

Thanks for the feedback. The +1 adds one day to the smaller time.

Govind.

a wrote:
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

You can also copy Bob's formula down the column. It will work with both
cases.

--
Regards
Roger Govier
<a> wrote in message news:[email protected]...
> thanks.
>
> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
> formula down the whole column.
>
> BTW, what does (time + 1) do to the smaller time?
>
> thnks.

thanks.

both formulae worked,
but I would prefer the IF statement even though its abit longer.
since it applies to both cases of A1 > B1 or B1 > A1, i can drag the formula
down the whole column.

BTW, what does (time + 1) do to the smaller time?

thnks.

=MOD(B1-A1,1)

--
HTH

Bob Phillips

<a> wrote in message news:[email protected]...
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
1:50
> excel gives me gibberish.
>
> thnks.
>

<a> wrote in message news:[email protected]...

> both formulae worked,
> but I would prefer the IF statement even though its abit longer.
> since it applies to both cases of A1 > B1 or B1 > A1, i can drag the
formula
> down the whole column.

and mine doesn't?

Hi,

Lets say your start time is in cell A1 and end time is in B1, use the
formula

=IF(B1<A1,B1+1-A1,B1-A1)

and format the cell in the time format you wish.

Regards

Govind.

a wrote:
> I have two times,
> 22:19 pm and 1:50 am
> obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 -
> 1:50
> excel gives me gibberish.
>
> thnks.

I have two times,
22:19 pm and 1:50 am
obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 - 1:50
excel gives me gibberish.

thnks.