Knowing if a time value is between two other time columns

**heytherejem** · 02-05-2019, 08:01 AM

I have a table containing two times, a start and a finish time for various jobs.
I have a second table containing the start time of other jobs, and my aim is to look at each start time in Table2, and see if it clashes with the times in Table 1.
So I want to look at each time in Table 2 and compare to all the times in Table 1. The result should be a Y for if the time in table 2 clashes with a time in table 1, and a N if it does not clash.

Attaches is the data, I tried a IF(AND but it doesn't seem to be acknowledging the times correctly and is giving a N when it shouldn't.

Please help!

**Olly** · 02-05-2019, 08:52 AM

Use the following formula in a new column of your second table:

Formula:

=IF(COUNTIFS(Table32[Time Started],"<="&2+[@ScheduleStartTime],Table32[TimeCompleted],">="&2+[@ScheduleStartTime])>0,"Y","N")

**heytherejem** · 02-05-2019, 10:39 AM

@Olly,

Thanks for the formula, i've applied it and it's working, but it's given 17 'N' and some of them are within the [Time Started] & [TimeCompleted] such as cell I5 where the scheduled start time is 20:50:00 and which clashes with almost all the dates in Table32. And I9 is 22:00:00 which also clashes with most of them.
I can only see these two examples as incorrect, but any idea why this might have happened? I can attach the data again if needs be...

Thanks!
Jemma

**Pepe Le Mokko** · 02-05-2019, 10:47 AM

And BTW, contents of col E are text, not real time

**heytherejem** · 02-05-2019, 10:55 AM

Hi Pepe, so if [ScheduleStartTime] in Table25 is within the time frame between [Time Started] and [TimeCompleted] in Table32 then I want to highlight these.
I know Total Duration is text and I forced Time Started to be 20:00:00 as it's there or thereabouts for every run.

**Bo_Ry** · 02-05-2019, 10:11 AM

Please try at I2 and drag down

=IF((H2>MOD(MIN(Table32[Time Started]),1))+(H2<MOD(MAX(Table32[TimeCompleted]),1)),"Y","N")

**Pepe Le Mokko** · 02-05-2019, 10:43 AM

What do you mean by "clashes"?

**Olly** · 02-05-2019, 10:57 AM

Your data is a little tricky.

Your times in the first table include a date element - this is how we know the TimeCompleted (e.g. 04:31) is actually AFTER the Time Started(e.g. 20:00)

The ScheduleStartTime in the second table doesn't have a date element - so we need to add a date part to it... this is why I tried using 2+[@ScheduleStartTime] However, that logic isn't quite right. Looks like we need to determine when you consider a day starts - as you're not treating midnight as the start of a new day.

So - given the time ranges, I'm guessing we can assume 12:00 midday is a reasonable cutover. We can build this in to the formula, to give us:

Formula:

=IF(COUNTIFS(Table32[Time Started],"<="&IF([@ScheduleStartTime] < TIME(12,0,0),2,1)+[@ScheduleStartTime],Table32[TimeCompleted],">="&IF([@ScheduleStartTime] < TIME(12,0,0),2,1)+[@ScheduleStartTime])>0,"Y","N")

That's pretty horrible!

**heytherejem** · 02-06-2019, 01:43 PM

Hi Olly, you are completely right. I figured out the date was important when I tried to create a graph of the data which failed miserably. Thanks for this, it totally makes sense!

Knowing if a time value is between two other time columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Re: Knowing if a time value is between two other time columns

Thread Information

Users Browsing this Thread

Similar Threads

Macro to Convert Time Format and Filter Into Columns By Time

Differential exact time required from two date & time columns withe non working hours vomi

Merge date and time columns without loosing time

[SOLVED] Calculate Time from different Date and Time columns

help with knowing how many patients were present in given time frame

[SOLVED] Obtaining elapsed time in decimal when time and date are in different columns - v2003

Formula to Calculate speed knowing distance, destination time and checkpoint times

Bookmarks

Bookmarks

Posting Permissions