time formula

[Guest]

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

 

[T. Valko]

Try this:

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

Format as [h]:mm

Biff

 

[Guest]

In C1 enter:
=IF(B1>A1,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)

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Thanks valko

Try this:

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

Format as [h]:mm

Biff

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

 

[Guest]

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

 

[Guest]

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.

 

[T. Valko]

=IF(B1>A1,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

 

[Guest]

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

 

[SteveW]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[David Biddulph]

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]

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

 

[SteveW]

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

 

[David Biddulph]

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

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