Help with Match/Vlookup/HLookup formula

[jamezsez]

Hi All,

I am having difficulty determining what type of formula to use for a problem I have.

I have a range of surveys that include a start and end time on a specific date. I also have a spreadsheet with an extract of device availability.

What I am trying to do is return a "Yes", "No" or "Partial" result to determine whether the device was out of action during the survey.

Example...

Survey ID: 1
Vehicle ID: 3035AO
Date: 2/4/13
Start time: 13:15
End time: 14:15

Availability
Vehicle ID: 3035AO
Alert text: Terminal Out of Order
Down Timestamp: 2/4/13 13:00
Up Timestamp: 2/4/12 15:00

In this example, the terminal was down for the entire length of the survey.

I have included a sample spreadsheet and linked below.

https://dl.dropboxusercontent.com/u/50218117/Device Available Check.xlsx


Thank you for your help.

 

[Robert Mika]

Hi All,

I am having difficulty determining what type of formula to use for a problem I have.

I have a range of surveys that include a start and end time on a specific date. I also have a spreadsheet with an extract of device availability.

What I am trying to do is return a "Yes", "No" or "Partial" result to determine whether the device was out of action during the survey.

Example...

Survey ID: 1
Vehicle ID: 3035AO
Date: 2/4/13
Start time: 13:15
End time: 14:15

Availability
Vehicle ID: 3035AO
Alert text: Terminal Out of Order
Down Timestamp: 2/4/13 13:00
Up Timestamp: 2/4/12 15:00

In this example, the terminal was down for the entire length of the survey.

I have included a sample spreadsheet and linked below.

https://dl.dropboxusercontent.com/u/50218117/Device%20Available%20Check.xlsx


Thank you for your help.

What's determine the Yes/No in cell F2 on the Survey Sample tab?
Otherwords how did you get this answer?

 

[jamezsez]

This is just an example of the expected result.

 

[Robert Mika]

This is just an example of the expected result.

So when do we get "Yes"?When "No" and when "Partial"?

 

[jamezsez]

The cell will return 'Yes' when the survey sample period is fully included in one of the downtime rows. (example, Survey runs from 1pm-2pm, and a device is down during the full period of time).

It will return 'No' when the survey sample period is not included in one of the downtime rows. (example, Survey runs from 1pm-2pm, and a device is working for the full period of time).

And it will return 'Partial', if the survey sample period is partially included in one of the downtime rows. (example, Survey runs from 1pm-2pm and a device is down from 130pm-2pm).