having trouble with time

Posted by Regan on December 02, 2000 9:08 AM

I'm really new to excel and I'm trying to figure this one out....
I'll use cells B1,B2 & B3 to eplain.
B1 is constant let's say 19:30
B2 changes daily
I need B3 to show the difference between the two, ie;
B1 = 19:30
B2 = 19:15
B3 = -:15

B1 = 19:30
B2 = 19:40
B3 = :10

PLEASE HELP !!!!

Posted by Skip Bisconer on December 02, 2000 11:10 AM

Try formatting your entire columns by selecting
Format, Cells, under the number tab select time.
pick the first time format...should be something like 13:30. If your earlier or beginning time is in cell A1 and your latest time is in B1 then your formula in C1 would be +B1-A1. This should result in the correct time difference in HH:MM format. Make sure you are subtracting a lower time from a higher time or you will get a buch of ########'s in C1

Posted by Regan on December 02, 2000 11:29 AM

Thanks Skip, but I need it to show a negative time value in that cell instead of all those #######'s. Is there any way to do that ?????

Posted by Celia on December 02, 2000 4:25 PM

Have a look at 1423.html

Celia

Posted by Regan on December 02, 2000 9:19 PM

Thank You Celia !!! :)

Posted by Regan on December 02, 2000 9:34 PM

This is actually what I needed ...
Thanks to all for your help 1

THE FORMULA :

1. Assign the Custom format 00":"00 to cell B3.
2. Change the formula in cell B3 to:

=IF(B2&LT;B1,-1,1)*((TRUNC(ABS(B2-B1)*24)*100)+MOD(ABS(B2-B1)*1440,60))

Posted by Regan on December 02, 2000 9:38 PM

Posted by Celia on December 03, 2000 12:53 AM

Regan
Using your formula when the time in B2 has the same number of minutes as the time in B1 but has different hours, it sometimes produces a result that shows the difference as xx:60 and sometimes as xx:00.
I can't work out why this sometimes happens (can anyone explain?) but I think the following fixes it :-
=IF(B2&LT;B1,-1,1)*((TRUNC(ABS(B2-B1)*24+0.01)*100)+(MOD(ROUND(ABS(B2-B1)*1440,0),60)))

Celia