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.
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.
=HOUR(AC2) in AG2 and copy down.
=MINUTE(AC2) in AH2 and copy down.
It isn't clear what you need in AI & AJ
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks CK76...
Can u please help me with the formula in AI.... ?
Please assist.
So what's all your buckets or category for that column? Without it, can't really help you with formula.
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
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?
Last edited by CK76; 10-29-2018 at 12:07 PM.
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)
Bookmarks