# Question about time formula with specific conditions

1. ## 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. ## 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:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Formula:
`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.

3. ## 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
?

@etaf

Good point.

5. ## 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.

6. ## Re: Question about time formula with specific conditions

Originally Posted by etaf
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.

7. ## Re: Question about time formula with specific conditions

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

Limited testing works.

8. ## Re: Question about time formula with specific conditions

Originally Posted by newdoverman
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. ## 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. ## Re: Question about time formula with specific conditions

Originally Posted by newdoverman
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!

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