+ Reply to Thread
Results 1 to 7 of 7

How to calculate the hour and min from Time

  1. #1
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    149

    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.

    Please assist.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,169

    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
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    149

    Re: How to calculate the hour and min from Time

    Thanks CK76...

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

    Please assist.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,169

    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. #5
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    149

    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. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,169

    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?
    Last edited by CK76; 10-29-2018 at 12:07 PM.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,169

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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