VLOOKUP Function to find date within Range and provide Yes/No

[bcarroll76]

Hi all,

I have a spreadsheet that among other data columns, has a long list of random dates/times example: 6/25/16 8:00:01 but all from the same month... I need to know if these dates and times fall in a range say 6/1/16 8:00:00 through 6/1/16 19:00:00 , 6/2/16 8:00:00 through 13:00 etc... Weekdays of coverage are always 08:00 to 19:00 and weekends are always 08:00 - 13:00:00 and if they do to give a YES or NO... Anything outside those times, I would need feedback of NO.... even better if the rows that are out of range could be hidden would be great.

I have went as far as to create a spreadsheet of Dates and Times down to the Second and unfortunately I am not savvy enough to figure out how to search multiple columns of data and as you can imagine, I reach excels column size limit when I try to put everything in one column.


My List of random times are on sheet 1 (along with multiple columns of data) and my list of dates/times to compare to is on sheet 2 and in multiple columns.. it doesnt seem efficient, I would rather just create a range say:

Sheet1Column 1 (data) ....Sheet2 Column1 ...... Sheet 2 Column2. ....Y/N
6/1/2016 09:45:05............ 6/1/2016 8:00:00 ...... 6/1/2016 19:00:59 ....Y
6/1/2016 19:52:00............6/2/2016 8:00:00 ......6/2/2016 19:00:59...... N
6/2/2016 10:05:00...........6/3/2016 8:00:00 ......6/3/2016 19:00:59.......Y
6/4/2016 13:56:00...........6/4/2016 8:00:00 ...... 6/4/2016 13:00:59......N

I would like the YES or NO answer to show up in a new row next to the list of data on sheet 1 (or the columns to be removed from 1st sheet )

Any help would be great-- simpler the easier as I will need to do this for the rest of the year and moving forward and hand the document to staff to run a Macro I create with this captured...

Thank you!
Bryan

[jason.b75]

Anything outside those times, I would need feedback of NO....

When comparing time ranges in the way that you are asking to, there are 5 possible outcomes.

Should we assume that any of the random times in the first sheet intersect with the reference range in whole, or in part, should return Yes?

If not please clarify which random times should be classified as Yes relative to the reference time.

A sample file showing the expected results is always helpful.

[FDibbins]

Try this...

	A	B	C
1	Col1	Col2
2	6/1/2016 8:00	6/1/2016 19:00	yes
3	6/2/2016 8:00	6/2/2016 19:00	yes
4	6/3/2016 8:00	6/3/2016 19:00	yes
5	6/4/2016 8:00	6/4/2016 13:00	yes

C2=IF(AND(ROUNDDOWN(MOD(A2,1)*24,0)>=8,ROUNDDOWN(MOD(B2,1)*24,0)<=IF(WEEKDAY(A2,2)=6,13,19)),"yes","no")
copied down

To hide, just apply filters and uncheck No

[bcarroll76]

If I am clear the on what you are asking, 1/2 of the random times or more will fall outside the ranges I mentioned. Anything that fall within the range references should return a YES all that fall outside the range would return a NO. The reference Date and time should match exactly within the Range to return a YES..

Hope that helps, I will try to upload a sample tmrw for reference

Thanks Jason

When comparing time ranges in the way that you are asking to, there are 5 possible outcomes.

Should we assume that any of the random times in the first sheet intersect with the reference range in whole, or in part, should return Yes?

If not please clarify which random times should be classified as Yes relative to the reference time.

A sample file showing the expected results is always helpful.

[bcarroll76]

In the below recommendation where are you referencing the colum that contains the random date/time data that we are trying to confirm falls in those ranges?

QUOTE=FDibbins;4441232]Try this...

	A	B	C
1	Col1	Col2
2	6/1/2016 8:00	6/1/2016 19:00	yes
3	6/2/2016 8:00	6/2/2016 19:00	yes
4	6/3/2016 8:00	6/3/2016 19:00	yes
5	6/4/2016 8:00	6/4/2016 13:00	yes

C2=IF(AND(ROUNDDOWN(MOD(A2,1)*24,0)>=8,ROUNDDOWN(MOD(B2,1)*24,0)<=IF(WEEKDAY(A2,2)=6,13,19)),"yes","no")
copied down

To hide, just apply filters and uncheck No[/QUOTE]

[bcarroll76]

I also made changes to my example in post 1..... Sheet1 Column 1 is my random reported dates/times . Sheet 2 column 1 and 2 are range start and ends...

[FDibbins]

In the below recommendation where are you referencing the colum that contains the random date/time data that we are trying to confirm falls in those ranges?

All I am doing is showing you how you would test for a time that would fall between those 2 ranges, I am not testing against anything else.

If that does not do what you want, please

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like (as was previously requested).

[bcarroll76]

Thanks FDibbins - I uploaded a test SS with example. Thanks all

[JohnTopley]

Using FDibbins formula from Post #3 (I removed the ROUNDDOWN):

in B2 and copy down

=IF(AND(MOD(A2,1)*24>=8,MOD(A2,1)*24<=IF(WEEKDAY(A2,2)=6,13,19)),"KEEP","DELETE")

[bcarroll76]

Looks good guys, seems to be working for the weekday range of 08:00 through 19:00 but the weekends are only until 13:00 how can we allow for that in the formula? I think that is my hurdle and why I may need to compare to a list of ranges for the month or a full blown calendar for the month all the way down to the second (which is huge)

[FDibbins]

Using FDibbins formula from Post #3 (I removed the ROUNDDOWN):

in B2 and copy down

=IF(AND(MOD(A2,1)*24>=8,MOD(A2,1)*24<=IF(WEEKDAY(A2,2)=6,13,19)),"KEEP","DELETE")

John, the reason I used the rounddown was because in the original file, 19:00 had a few seconds to it, so that 19:00 <> 19/24 - looks like the latest file does not have that problem

[FDibbins]

Looks good guys, seems to be working for the weekday range of 08:00 through 19:00 but the weekends are only until 13:00 how can we allow for that in the formula? I think that is my hurdle and why I may need to compare to a list of ranges for the month or a full blown calendar for the month all the way down to the second (which is huge)

The formula should handle that too, that is what the IF statement in there does?

[bcarroll76]

I ran it down the list of date in my example above an on 6/5/2016 it is showing "KEEP" for hours above 13:00 it looks to happen on the second day of the weekend.

[jason.b75]

Try this in B2 and fill down

=IF(MOD(A2,1)=MEDIAN(MOD(A2,1),--"08:00",--IF(WEEKDAY(A2,2)<=5,"19:00","13:00")),"Keep","Delete")

Sheet2 not needed, all done within the formula.

[bcarroll76]

Bingo! Thanks for everyones help!

[FDibbins]

happy to help

[bcarroll76]

Can someone break down the formula so I know what I can change moving forward if our date/time ranges need to change?

For instance, I now have the need to allow for a range of 16:00 through 08:00 (the next day) KEEP and then DELETE anything outside of that range on Weekdays and all day KEEP on weekends.

Here is the previous formula that is working for me:

=IF(MOD(P24,1)=MEDIAN(MOD(P24,1),--"08:00",--IF(WEEKDAY(P24,2)<=5,"19:00","13:00")),"Keep","Delete")


Also, is there any way to alott for coverage during major Holidays? Depending on the formula, we would need coverage from 08:00 through 13:00 or All day coverage for New Years, Memorial Day, July 4th, Labor day, Christmas.

I think that may be asking a bit much...

Thanks gurus!

[jason.b75]

The WEEKDAY() part checks the day of the week for the date in the cell, Monday=1, Sunday=7.

--"08:00" defines the common start time.

--IF(WEEKDAY(P24,2)<=5,"19:00","13:00")) sets the end time for the relevant weekday <=5 (less than Friday) ="19:00", else "13:00" (greater than Friday. i.e. weekends).

These times, along with the time in the cell are passed to the MEDIAN function, the formula then checks that the MEDIAN (the middle of the 3 values) is the same as the time in the cell (Times before 08:00 will return 08:00 as the median, which will not match the time in the cell, similarly times after the defined end time will cause MEDIAN to return the end time, again this will not match the actual time).

The variations in coverage for major holidays can be incorporated, but you would need a list of the dates to cross reference. Would you be able to enter then into a range of cells somewhere so that the formula can refer to them?

[bcarroll76]

Thank you for the explanation Jason! Very much appreciated!

Yes, I could create a list of the dates/times for Holidays to cross reference. I have a DATA! sheet that I could place them in for reference.

How would they need to be listed and how would I reference them?

Appreciate the help!

[jason.b75]

Hi Bryan,

Sample file for you with an example method, all done on one sheet for testing, but table can be moved to another sheet, hopefully it makes sense.

[bcarroll76]

That worked perfect for hours that fall in a single day, so I have incorporated that.

What if hours covered fall between 2 days? For instance if we cover from 19:00 Monday until 06:30 Tuesday and 13:00 on Saturday until 07:00 Sunday...

Weekdays are 19:00 - 6:30 the next day

Weekends are 13:00 Friday until 07:00 Saturday, 13:00 Saturday through 07:00 Sunday, 13:00 Sunday through 06:30 Monday....

Would I be able to do something like attached?

Thanks again!

[jason.b75]

I haven't looked at all of the times in detail, but it appears that this example is the reverse of the previous one, i.e. keep anything that was previously deleted.

If that is the case, then simply swap "Keep" and "Delete" in the formula, using the times in the table as a reference for those to delete instead of keep.

Does that work (with and without all day cover) or have I missed something?

[bcarroll76]

Thanks for looking Jason!

The only thing is that we dont cover from 6:30 until 8 on weekdays or from 7 to 8 on weekends.

Thats the wrench

I dont mind it being a separate Keep/Delete Column and Foormula / Table for this coverage.

[jason.b75]

In that case I stand by my suggestion, just modified slightly to match what is needed.

Create a second table, using the same format as the file I attached to post #20, but with the times to be deleted rather than kept.

Note that you will need to adjust the times in the table by 1 second so that any of the times in the random list which fall exactly on the start / finish time still give the correct result.

Start of the period to delete should be 1 second earlier (i.e. use 07:59:59 instead of 08:00), end of the period to delete should be 1 second later.(i.e. 08:00:01).

Use the same formula as the sample file, but with the ranges directed to the new table, "Keep" and "Delete" in the formula need to be swapped.

Hope that makes sense.