Need formula logic understanding..

[VBABEGINER]

I need logic understanding on this..
I have this value in my column..

227:34:55..a1
83:57:48..a2

also

17:57:03..a3
14:05:02..a4

While using this formula, I'm getting answer like this..

=IF(A2="MR",HOUR(A2),"")

227:34:55..a1 - Answer = 11
83:57:48..a2 - Answer = 11

also

17:57:03..a3 - Answer = 17
14:05:02..a4 - Answer = 14

Why I'm getting 11 on a1 and a2...where I getting correct on a3 and a4..

 

[philwojo]

Because it is returning the hour value it is in a 24 hour format. If you break it out 227 starts in the 216th hour (counting by 24's). So if you start at 216, 227-216 = the 11th hour.

Same for 83, it starts in the 72nd hour, so 83-72 =11.

 

[Rick Rothstein]

If you read the help file for the worksheet's HOUR function, it says...

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

To get the hour value that you are after, use this formula...

=INT(24*A1)

 

[VBABEGINER]

Hey, Thanks for reply..
In that case, I have this value also..

11:23:56

..
So how do i write my formula, which will give me, am and pm value correctly..
do you have idea..

Because it is returning the hour value it is in a 24 hour format. If you break it out 227 starts in the 216th hour (counting by 24's). So if you start at 216, 227-216 = the 11th hour.

Same for 83, it starts in the 72nd hour, so 83-72 =11.

 

[Rick Rothstein]

Hey, Thanks for reply..
In that case, I have this value also....
So how do i write my formula, which will give me, am and pm value correctly..
do you have idea..

How does your AM/PM request even begin to apply to a value like 227:34:55... exactly what are you trying to do to the values in Column A with your formula to extract the hour from it?

 

[VBABEGINER]

...Ok, I got it numbers now...That is Hours completed for task..But...

Hi Sir..I did not notice your post..Let me check..

If you read the help file for the worksheet's HOUR function, it says...

To get the hour value that you are after, use this formula...

=INT(24*A1)

 

[VBABEGINER]

ok, how will I write this formula..

[/COLOR]=IF(A2="MR",INT(24*A2),"")[COLOR=#ff0000]

instead of, If A2 = MR, then it also check, if This INT answer is less than 8, then "0 - 8 hrs" elseIf, greater than 8 and less than 16, then "8 - 16 Hrs" and elseIf, greater than 16 and less then 24, then "16 - 24 Hrs" ELSE, "<24 Hrs".....

 

[Rick Rothstein]

[/COLOR]=IF(A2="MR",INT(24*A2),"")[COLOR=#ff0000]

This is the second time you have written the above formula... I am sorry, but it makes no sense to me. It says that if cell A2 has "MR" in it, then multiply the "MR" by 24 and take the INT of that... you cannot multiply "MR" by 24 and get any numerical answer. Did you, perhaps, mean if cell A2 has "MR" in it, the return "", otherwise assume it has a number in it and multiply it by 24 and then take the INT of it?

 

[VBABEGINER]

Hi, Thanks for reply..
I'm checking two condition.

1st, if cell b2 contains MR..
2nd, if b2 is MR, and an "INT(24*A2).....

Ohh....Big apologies....Total my mistake.......from starting of the post, I wriiten..=if(a2="MR",....etc...This is wrong pls..

Its, If(b2="MR",INT(24*a2),...and condition's..) and a2 contains this values..(83:57:48)

AGAIN...

I'm checking two condition.

1st, if cell b2 contains MR..
2nd, if b2 is MR, and an "INT(24*A2) is less than 8, then output should be "0 - 8 hrs"
elseIf, greater than 8 and less than 16, then output should be "8 - 16 Hrs"
elseIf, greater than 16 and less then 24, then output should be "16 - 24 Hrs"
ELSE, output should be "greater than 24 Hrs"

This is the second time you have written the above formula... I am sorry, but it makes no sense to me. It says that if cell A2 has "MR" in it, then multiply the "MR" by 24 and take the INT of that... you cannot multiply "MR" by 24 and get any numerical answer. Did you, perhaps, mean if cell A2 has "MR" in it, the return "", otherwise assume it has a number in it and multiply it by 24 and then take the INT of it?