+ Reply to Thread
Results 1 to 10 of 10

Question about time formula with specific conditions

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    Brew City
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question about time formula with specific conditions

    I have a set of data with two columns:
    Column A: Group
    Column B: Time
    Sample data:
    Group Time
    A 9:30 PM
    A 10:30 PM
    A 3:25 AM
    A 1:05 AM
    B 10:00 PM
    B 10:30 PM
    B 2:25 AM
    B 11:15 PM

    The goal is to find the earliest and the latest time by each group. I am having problem using the MIN and MAX functions as the desired result I am looking for consider the time before midnight as "early" and after midnight as "late". The time data set is usually in the time interval between 7:00 PM through 4:00 AM, not sure if this helps.

    Desired result:

    Earliest Time Latest Time
    A 9:30 PM 3:25 AM
    B 10:00 PM 2:25 AM

    I wonder if anyone has done similar logic with time data before. I appreciate the input in advance. Thank you!

    -HL

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Question about time formula with specific conditions

    These formulae will give you the early and late times for A and B. Enter with Ctrl + Shift + Enter and format as time. If you want to be able to select any group easily, replace the "A" and "B" with a cell reference that will have the group ID.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you include the date in the time, it would make it easier to determine early or late that crosses midnight. For example 11:59 PM might be the early time with 2:00 AM being the late time.
    Last edited by newdoverman; 02-10-2014 at 12:31 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Question about time formula with specific conditions

    what is your range for early or late

    as a time after midnight , could be both early or late

    what range of hours would be early
    and what range would be late

    5am in your example could be early or late ?

    you say
    time interval between 7:00 PM through 4:00 AM, not sure if this helps.

    so is 7pm to midnight early
    and
    midinight+1minute to 4am late
    ?
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Question about time formula with specific conditions

    @etaf

    Good point.

  5. #5
    Registered User
    Join Date
    04-23-2012
    Location
    Brew City
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Question about time formula with specific conditions

    Hi there,

    Thanks for your reply. This set of data is for departure time from a city. The departure time are in the interval between 7pm through 4am the next morning.

    Time interval: (7,8,9,10,11,0,1,2,3,4)

    The goal is to look for the earliest and latest departure time. In this case, the earliest departure time should be 7pm, and latest should be 4am. If I use the MIN(7,8,9,10,11,0,1,2,3,4), it will return 0:00 - midnight, if I use MAX(7,8,9,10,11,0,1,2,3,4), the result will show 11.

    Please refer to the original thread I posted shows additional examples based on the time interval.

    Thanks for your input.

  6. #6
    Registered User
    Join Date
    04-23-2012
    Location
    Brew City
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Question about time formula with specific conditions

    Quote Originally Posted by etaf View Post
    what is your range for early or late

    as a time after midnight , could be both early or late

    what range of hours would be early
    and what range would be late

    5am in your example could be early or late ?

    you say
    time interval between 7:00 PM through 4:00 AM, not sure if this helps.

    so is 7pm to midnight early
    and
    midinight+1minute to 4am late
    ?
    Hi there,

    Thanks for your reply. This set of data is for departure time from a city. The departure time are in the interval between 7pm through 4am the next morning.

    Time interval: (7,8,9,10,11,0,1,2,3,4)

    The goal is to look for the earliest and latest departure time. In this case, the earliest departure time should be 7pm, and latest should be 4am. If I use the MIN(7,8,9,10,11,0,1,2,3,4), it will return 0:00 - midnight, if I use MAX(7,8,9,10,11,0,1,2,3,4), the result will show 11.

    Please refer to the original thread I posted shows additional examples based on the time interval.

    Thanks for your input.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Question about time formula with specific conditions

    This might do what you want. It uses a helper column C.

    Limited testing works.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-23-2012
    Location
    Brew City
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Question about time formula with specific conditions

    Quote Originally Posted by newdoverman View Post
    This might do what you want. It uses a helper column C.

    Limited testing works.
    Yes, the new assignment in column C will be helpful. Thank you for your help. I appreciate it.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Question about time formula with specific conditions

    You're welcome, I hope that it works for you. As with my previous post, you can change the "A" and "B" references with cell references so that if/when you have other entries, you can easily get the results for those entries.

  10. #10
    Registered User
    Join Date
    04-23-2012
    Location
    Brew City
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Question about time formula with specific conditions

    Quote Originally Posted by newdoverman View Post
    You're welcome, I hope that it works for you. As with my previous post, you can change the "A" and "B" references with cell references so that if/when you have other entries, you can easily get the results for those entries.
    I tried it with If(and) function since i have to match two different criteria and it seemed to work fine. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Specific conditions (values) make a formula bug
    By pitinswiss in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2014, 12:39 PM
  2. Replies: 10
    Last Post: 12-10-2012, 01:39 PM
  3. Adding Time According to Specific Conditions
    By dizzygirl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2012, 12:23 PM
  4. [SOLVED] an excel formula for specific conditions
    By srinivasan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-27-2006, 01:25 AM
  5. [SOLVED] RE: an excel formula for specific conditions
    By Franksta in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-26-2006, 06:55 PM

Tags for this Thread

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