# How to calculate the hour and min from Time

1. ## How to calculate the hour and min from Time

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.  Register To Reply

2. ## Re: How to calculate the hour and min from Time

=HOUR(AC2) in AG2 and copy down.

=MINUTE(AC2) in AH2 and copy down.

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

3. ## Re: How to calculate the hour and min from Time

Thanks CK76...  Register To Reply

4. ## Re: How to calculate the hour and min from Time

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

5. ## Re: How to calculate the hour and min from Time

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  Register To Reply

6. ## Re: How to calculate the hour and min from Time

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?  Register To Reply

7. ## Re: How to calculate the hour and min from Time

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})  Register To Reply