How to calculate the hour and min from Time

[lalaarif1]

Dear Team,

PFA.

Is there any formula which can calculate Hours and minutes from the list of times (required output is mentioned in col. AG to AJ) ?

Data is huge and manual excercise is very time consuming.

Please assist.

[CK76]

=HOUR(AC2) in AG2 and copy down.

=MINUTE(AC2) in AH2 and copy down.

It isn't clear what you need in AI & AJ

[lalaarif1]

Thanks CK76...

Can u please help me with the formula in AI.... ?

Please assist.

[CK76]

So what's all your buckets or category for that column? Without it, can't really help you with formula.

[lalaarif1]

Catagories are

>= 8:00:00 hrs & <= 10:59:59 hrs, then 0
> 11:00:00 hrs & <= 12.59:59 hrs, then 2
> 13:00:00 hrs & <= 16.29:59 hrs, then 5
> 16:30:001 hrs, then 10

[CK76]

Your condition is excluding 11:00:00 on the dot etc.
Should it not be...?
>=8:00:00 & <=11:00:00 then 0
>11:00:00 & <=13:00:00 then 2
>13:00:00 & <=16:30:00 Then 5
>16:30:00 then 10

Edit: Oh, is there any chance of value being <8:00:00?

[CK76]

Assuming above assumption is correct.

I'd use lookup table in AO2:AP5.
See attached.

NOTE: You have AC2:AC5 as text, but AC6 is in timevalue. For consistency I've stored AC6 as text as well.

If you'd rather hardcode lookup table.
=LOOKUP(TIMEVALUE(AC2),{"8:00:00","11:00:01","13:00:01","16:30:01"},{0,2,5,10})