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.

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

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

Thanks CK76...

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.

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

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?

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})

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1