Find if a date is between 2 list of dates

[Daniel]

I'm working on my team oncall metrics.
I have the date when starts and finish the oncall. So I have a list with all the oncalls when starts and when it finish.
Also I have a list with the date and the incident and which team was called to resolve the incident.

I would like to know if its possible, with the incident date match the date between the 2 list and copy who was the oncall.

I manage to do it for one day oncall, but I couldn't do it with a list.

I have attached an example of the spreadsheet.


Thanks,
Regards,
Daniel

 

[SirJB7]

Hi, Daniel!

Your start and end dates overlap, so for example it isn't possible to decide which oncall corresponds to 16/09/2013. Then assuming that are weekly shifts that range from 09/09 thru 15/09 instead of 16/09, you could try this in worksheet "pages":
C5: =BUSCARV($B5;oncall!$B$3:$E$9;4;VERDADERO) -----> in english: =VLOOKUP($B5,oncall!$B$3:$E$9,4,TRUE)
D5: =BUSCARV($B5;oncall!$B$3:$E$9;3;VERDADERO) -----> in english: =VLOOKUP($B5,oncall!$B$3:$E$9,3,TRUE)

Just advise if any issue.

Regards!

PS: BTW, nice date that of B7, remember me to send you a gift on it

 

[Daniel]

It worked excellent.
Now, I tried to work with it. We have 3 teams, so when I doing the metrics I have to check for team also.
So from the original function =VLOOKUP($B5,oncall!$B$3:$E$9,4,TRUE)

I end up with =IF(AND(NOT(ISNA(VLOOKUP($B5;oncall!$B$3:$E$9;3;TRUE)));IF($C5=VLOOKUP($B5;oncall!$B$3:$E$9;4;TRUE);TRUE;FALSE));VLOOKUP($B5;oncall!$B$3:$E$9;3;TRUE);FALSE)

The function works, but I would like to know if that could be done simpler.
So let me explain each part of the function
NOT(ISNA(VLOOKUP($B5;oncall!$B$3:$E$9;3;TRUE)) --> Returns True/False if VLOOKUP found someone who worked that day.

IF($C5=VLOOKUP($B5;oncall!$B$3:$E$9;4;TRUE);TRUE;FALSE) --> Returns True/False if the team who answer the incident is the same as the member of the team found

VLOOKUP($B5;oncall!$B$3:$E$9;3;TRUE) --> Returns the team member.

Thanks,
Daniel

 

[SirJB7]

Hi, Daniel!

I do understand your formulas but I don't understand what do you want to achieve, since your sample data isn't as complete as desirable and may lead to errors.

Confirm this: do you have 3 teams that work a full week each or do you have 3 teams that may work simultaneously?

For the formulas provided for columns C:D I used the 1st criteria, that's to say, given a date, the team and the oncall should be retrieved. If the 2nd criteria is the correct one, then there'll be a unique formula (if available, otherwise a UDF, user defined function) for column D that given a date and a team then only the oncall should be retrieved.

Could you please elaborate?

Regards!

 

[Daniel]

You are correct, sorry for the little explanation.
For the question, yes, I have 3 team simultaneously. Basically I work in the middleware area, so everything that is not a OS or Database comes my way. There are 3 teams (Web, MQ, Security). So in the same week there are 3 different oncall.
I have a list will all the incidents and another list with all the oncalls.
The idea is to match the date and the team and then copy the oncall to the pages

I tried to use the formula I put before and it didn't work. I believe that when if finds the first error stop testing.

I have attached a more realistic spreadsheet.

 

[SirJB7]

Hi, Daniel!
With such environment if I were you (or your boss) I'd have emailed Development guy's boss a fulfilled requirement form for a new application...
So your input arguments are date and team and your desired output argument is oncall, am I right?
Regards!

 

[Daniel]

True enough But sadly I can't do it.
Anyway, I found a nasty workaround.

I used the formula I came with before =IF(AND(NOT(ISNA(VLOOKUP($B5;oncall!$B$3:$E$9;3;TRUE)));IF($C5=VLOOKUP($B5;oncall!$B$3:$E$9;4;TRUE);TRUE;FALSE));VLOOKUP($B5;oncall!$B$3:$E$9;3;TRUE);FALSE)

On the oncall page, I separated the 3 teams in columns for each team.
The pages spreadsheet is usually long. So I filter by team and used the formula adapted by team.

A nasty workaround, but it works

 

[Faseeh]

Hi Daniel,

Referring to your data, the dates in row 4 and 6 are same with but different teams, is that correct ? If that is incorrect, try this formula:

=INDEX(oncall!$D$3:$D$8,SUMPRODUCT((B5>=oncall!$B$3:$B$8)*(B5<=oncall!$C$3:$C$8)*(oncall!$E$3:$E$8=pages!C5)*ROW($A$1:$A$6)))