Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula to do some calculations with time. | Excel Worksheet Functions | |||
can excel calculate time increments in a formula? | Excel Worksheet Functions | |||
formula to determine time range overlap? | Excel Discussion (Misc queries) | |||
Help with time formula so the time will not change. | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |