time formula
 

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Try this:

=IF(COUNT(A1:B1)<2,"",B1-A1+(B1<A1))

Format as [h]:mm

Biff

"crusty53" wrote in message
...
i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

In C1 enter:
=IF(B1A1,B1-A1,B1+1-A1)
and copy down. you should see:

16:00 0:30 8:30
15:43 23:49 8:06
13:55 0:01 10:06


We need B1+1 to push the time ahead to the next day (+24 hours)
--
Gary's Student


"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

C1 =(B1-A1)+(A1B1)
copy from c1 down as far as needed

"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Thanks i tried this and got an# error
but thanks for the try.
i got an answer from garys student that worked so until the next querie
thank u again.

"Teethless mama" wrote:

C1 =(B1-A1)+(A1B1)
copy from c1 down as far as needed

"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Thanks valko

"T. Valko" wrote:

Try this:

=IF(COUNT(A1:B1)<2,"",B1-A1+(B1<A1))

Format as [h]:mm

Biff

"crusty53" wrote in message
...
i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Thanks dont quite understand why this works ,but it does.
So thanks again until i get confused again.

"Gary''s Student" wrote:

In C1 enter:
=IF(B1A1,B1-A1,B1+1-A1)
and copy down. you should see:

16:00 0:30 8:30
15:43 23:49 8:06
13:55 0:01 10:06


We need B1+1 to push the time ahead to the next day (+24 hours)
--
Gary's Student


"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Gary this works great ,but when there is nothing in the columns then it gives
a value error how do i get rid of this please.

"Gary''s Student" wrote:

In C1 enter:
=IF(B1A1,B1-A1,B1+1-A1)
and copy down. you should see:

16:00 0:30 8:30
15:43 23:49 8:06
13:55 0:01 10:06


We need B1+1 to push the time ahead to the next day (+24 hours)
--
Gary's Student


"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

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

If there's nothing in either cell (cells are empty) the formula should
return 1, not an error. So, that tells me that the cells really aren't
empty.

The formula I posted accounts for *empty* cells.

Biff

"crusty53" wrote in message
...
Gary this works great ,but when there is nothing in the columns then it
gives
a value error how do i get rid of this please.

"Gary''s Student" wrote:

In C1 enter:
=IF(B1A1,B1-A1,B1+1-A1)
and copy down. you should see:

16:00 0:30 8:30
15:43 23:49 8:06
13:55 0:01 10:06


We need B1+1 to push the time ahead to the next day (+24 hours)
--
Gary's Student


"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me
15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Beautiful,because i still dont understand these things properly YET
i have to try all of them and yours works great ,even in the empty cells :-)

"T. Valko" wrote:

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

If there's nothing in either cell (cells are empty) the formula should
return 1, not an error. So, that tells me that the cells really aren't
empty.

The formula I posted accounts for *empty* cells.

Biff

"crusty53" wrote in message
...
Gary this works great ,but when there is nothing in the columns then it
gives
a value error how do i get rid of this please.

"Gary''s Student" wrote:

In C1 enter:
=IF(B1A1,B1-A1,B1+1-A1)
and copy down. you should see:

16:00 0:30 8:30
15:43 23:49 8:06
13:55 0:01 10:06


We need B1+1 to push the time ahead to the next day (+24 hours)
--
Gary's Student


"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me
15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

It works, because you are adding in an extra 24 hours (1 day) if the
sign ooff time is a day later than the sign on time.
You have to give Excel some chance of understanding the values that you
have
entered.

A different way would be to to enter the sign on date+time
and the sign off+time, but this is cumbersome and so you need
to adjust the simple b1-a1 formula to cover the situation when sign off is
the
next day.

Stev



On Sun, 24 Dec 2006 08:44:00 -0000, crusty53
wrote:

Beautiful,because i still dont understand these things properly YET
i have to try all of them and yours works great ,even in the empty cells
:-)

"T. Valko" wrote:

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

If there's nothing in either cell (cells are empty) the formula should
return 1, not an error. So, that tells me that the cells really aren't
empty.

The formula I posted accounts for *empty* cells.

Biff

"crusty53" wrote in message
...
Gary this works great ,but when there is nothing in the columns then
it
gives
a value error how do i get rid of this please.

"Gary''s Student" wrote:

In C1 enter:
=IF(B1A1,B1-A1,B1+1-A1)
and copy down. you should see:

16:00 0:30 8:30
15:43 23:49 8:06
13:55 0:01 10:06


We need B1+1 to push the time ahead to the next day (+24 hours)
--
Gary's Student


"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me
15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Hi There!
so in cell C you write this =B1-A1+(B1<A1)*24 AND REPEAT THIS IN C2, C3, ...
TRY THIS I HOPE IT WILL HELP
SHARIQ

"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

HI CRUSTY 53!

IN CELL C1 ENTER (=B1-A1+(B1<A1)*24) AND COPY DOWN.....
WELL TRY WHAT I'VE SUGGESTED, IT WON'T DISAPPOINT YOU, IT'S EASY AND SIMPLE
NO NEED TO ENTER BIG LENGHTY FORMULAS IF YOU CAN GET IT DONE WITH A SIMPLE
SMALL ONE, YOU CAN COUNT TIME BY THIS FORMULA, EVEN IF FINISHING TIME IS INTO
NET DAY, AND WHEN NO TIME IS ENTERED THE C COLOUM SHOWS 0:00, BY THE WAY
YOU'LL HAVE TO FORMAT THE CELLS INTO TIME BEFORE YOU EXPECT YOU GET DESIRED
RESULTS.

SHARIQ

"crusty53" wrote:

Thanks i tried this and got an# error
but thanks for the try.
i got an answer from garys student that worked so until the next querie
thank u again.

"Teethless mama" wrote:

C1 =(B1-A1)+(A1B1)
copy from c1 down as far as needed

"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Here is another one:

=MOD(B1-A1,1)

Not sure what you want to show if one or both of the cells is blank

=if(countblank(A1:B1)0,"",MOD(B1-A1,1))

--
Regards,
Tom Ogilvy

"crusty53" wrote in message
...
i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Why would you want to multiply (B1<A1) by 24, but not multiply the B1-A1
part? If you are going to convert the answer to hours (and format as number
or general), then you'll need to multiply the lot by 24. If you are going
to format the answer as time then you don't need to multiply any of it by
24.
--
David Biddulph

"Shariq" wrote in message
...
Hi There!
so in cell C you write this =B1-A1+(B1<A1)*24 AND REPEAT THIS IN C2, C3,
...
TRY THIS I HOPE IT WILL HELP
SHARIQ

"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me 15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

Still wrong, see my reply to your previous post. SHOUTING won't make it
right, and is frowned upon by netiquette.
--
David Biddulph

"Shariq" wrote in message
...
HI CRUSTY 53!

IN CELL C1 ENTER (=B1-A1+(B1<A1)*24) AND COPY DOWN.....
WELL TRY WHAT I'VE SUGGESTED, IT WON'T DISAPPOINT YOU, IT'S EASY AND
SIMPLE
NO NEED TO ENTER BIG LENGHTY FORMULAS IF YOU CAN GET IT DONE WITH A SIMPLE
SMALL ONE, YOU CAN COUNT TIME BY THIS FORMULA, EVEN IF FINISHING TIME IS
INTO
NET DAY, AND WHEN NO TIME IS ENTERED THE C COLOUM SHOWS 0:00, BY THE WAY
YOU'LL HAVE TO FORMAT THE CELLS INTO TIME BEFORE YOU EXPECT YOU GET
DESIRED
RESULTS.

SHARIQ

"crusty53" wrote:

Thanks i tried this and got an# error
but thanks for the try.
i got an answer from garys student that worked so until the next querie
thank u again.

"Teethless mama" wrote:

C1 =(B1-A1)+(A1B1)
copy from c1 down as far as needed

"crusty53" wrote:

i have problems getting correct time.
A B C
1 16:00 00:30
2 15:43 23:49
3 13:55 00:01
In column C i want the time between A & B. But it keeps giving me
15:30
and it should be 8:30 Can anybody help.
The formula i am using is (B1-A1)
A is sign on time
B is sign off time

time formula
 

It's a trick.
(B1<A1) will either be True or False, its a boolean test
True or False will give 1 or 0, then *24 means that it effectively adds 24
hours
if B1 is less than A1

When starting with Excel stick to the IF() format if you aren't sure

You'r point about the hours / format etc is valid
and it is always necessary to keep the units of the various components the
same
ie 1 formatted as hh:mm will be 00:00 as it will treat it as 1 day !
[h]:mm would show it as 24:00

Steve

On Sun, 24 Dec 2006 15:15:56 -0000, David Biddulph
wrote:

Why would you want to multiply (B1<A1) by 24, but not multiply the B1-A1
part? If you are going to convert the answer to hours (and format as
number
or general), then you'll need to multiply the lot by 24. If you are
going
to format the answer as time then you don't need to multiply any of it by
24.

time formula
 

Exactly! You only need *24 on the Boolean to add 24 hours if the (B1-A1)
has also been multiplied by 24 to convert it to hours.

If he's going to leave the result formatted as time, then the formula is
=B1-A1+(B1<A1), but if he wants to format the result as general or number to
give hours then the formula is =(B1-A1+(B1<A1))*24

If he formats the results as time like h:mm then he can get away with his
formula of =B1-A1+(B1<A1)*24 because the Boolean term is adding 24 days
instead of 1 day, but the extra 23 whole days are lost in the formatting as
time, but, as you imply, if you format as [h]:mm to allow C1:C3 to be added
and deal with a total of greater than 24 hours then he will see that he's
got a silly result from his formula. His formula totals to 1130:42 for the
3 days, instead of the correct answer of 26:42.
--
David Biddulph

"SteveW" wrote in message
news:op.tk2s6sejevjsnp@enigma03...
It's a trick.
(B1<A1) will either be True or False, its a boolean test
True or False will give 1 or 0, then *24 means that it effectively adds 24
hours
if B1 is less than A1

When starting with Excel stick to the IF() format if you aren't sure

You'r point about the hours / format etc is valid
and it is always necessary to keep the units of the various components the
same
ie 1 formatted as hh:mm will be 00:00 as it will treat it as 1 day !
[h]:mm would show it as 24:00

Steve

On Sun, 24 Dec 2006 15:15:56 -0000, David Biddulph
wrote:

Why would you want to multiply (B1<A1) by 24, but not multiply the B1-A1
part? If you are going to convert the answer to hours (and format as
number
or general), then you'll need to multiply the lot by 24. If you are
going
to format the answer as time then you don't need to multiply any of it by
24.