How can we check whether the given time is between the working hours or not?
Is this Time between Working Hours
In our case, work starts at 9:30 AM, and ends at 5:30 PM. Our given time, which is 6:47 isn’t between the working ours. When we change it, we can see that the color changed into red and ‘No’ turned into ‘Yes’, which means that now the time is between the working ours (Fig. 1)
Fig. 1 Our given time is between the working hours
However, when we go to earlier hours we can see that the details changed (Fig. 2)
Fig. 2 Our given time isn’t between the working hours
How can we check our time? The simplest solution is using the MEDIAN function that returns the value in the middle. Since all our times are in one row, we can just select the range (Fig. 3)
=MEDIAN(B2:D2)
Fig. 3 Selecting the whole range
If our data is in separate cells, we can just add those cells one by one (Fig. 4)
=MEDIAN(B2,C2,D2)
Fig. 4 Selecting cells one by one
And just like that we receive the value in the middle. If this value is equal to the given time, it means it’s between the working hours. What’s curious about our median is the fact that it won’t cross our thresholds, i.e. our starting time and finishing time. It means that the smallest value our MEDIAN function can show is the starting hour, and the largest is the finishing hour (Fig. 5)
Fig. 5 The smallest value
Now, we have to check whether our time is between the working hours. We have to compare the function to the given time (Fig. 6)
=MEDIAN(B2,C2,D2)=B2
Fig. 6 Comparing
As we can see, our logical test returned FALSE (Fig. 7)
Fig. 7 FALSE
If the logical test returns TRUE, it means that the given time is between the working ours (Fig. 8)
Fig. 8 TRUE
The answers we have are logical. Let’s make them more human like by adding the IF function to our MEDIAN logical test and some simple text like ‘Yes’ and ‘No’ (Fig. 9)
=IF(MEDIAN(B2,C2,D2)=B2,“Yes”,“No”)
Fig. 9 Adding the IF function
We can see that we can check whether our given time is between the working ours or no (Fig. 10)
Sometimes, we want to find all the matched values and combine them into one text string, i.e., we want o VLOOKUP all matched values and then combine them.
VLOOKUP all matches and combine them
I will give you two solutions. The first solution will work from Excel 2019, and the second one will work in Dynamic Array Excel. First of all, we have to check if our representative comes from the country we are looking at. Let’s take a representative from Spain. There is only one representative of Spain in our example. We can check it using the IF function, where we have to create a proper logical test. We have to take into consideration the Country column, so let’s write it by selecting the column and clicking the F4 key to lock it. Then, we have to check if the values from the column are equal to just one country (Spain) by clicking the cell with the name of Spain (D4). It means that cell D4 will be compared to each cell from the Country column individually (Fig. 1)
Fig. 1 Comparison to each cell
The logical test will return TRUE only in the case of Spain. In the case of other countries, it will return FALSE. If the logical test is in an array, we can choose proper, corresponding result as a return value. We just need to select an array of the same size, which, in our case, is the Representative column. Then, we click the F4 key to lock it. When the logical test returns FALSE, we want to see an empty text string, so we have to write a space in double quotes. Let’s close the function with a parenthesis (Fig. 2)
=IF($A$2:$A$14=D2,$B$2:$B$14,” ”)
Fig. 2 IF function
Since I’m working in Dynamic Array Excel, Excel spills the results to the whole column (Fig. 3).
Fig. 3 IF function results
We can see that we have only one representative of Spain, and many empty cells. If we want to find the remaining representatives, we have to join the information we have. To do that, we can use the TEXTJOIN function, which is available from Excel 2019. We have to give the function a delimiter. Let’s write a comma and a space in double quotes. Then, we have to decide whether we want to ignore empty cells. We definitely do, so let’s write TRUE, or its shorter version, which is 1. Then, we just have to close the whole formula once again (Fig. 4).
After entering the formula and copying it down, we have our results (Fig. 5).
Fig. 5 TEXTJOIN function results
When we are in Dynamic Array Excel, we can use the FILTER function instead of the IF function. This function just filters our data. Let’s write FILTER function. Our data is located only in one column, which is the Representative column, so we have write the range and lock it with the F4key. Then, we have to create an array of the same size with logical values of TRUE or FALSE. It means that we have to create the same logical test as we did with the IF function. Let’s add the Country column (A2:A14), F4key to lock it, then an equal sign to check whether any of the value from the Country column is equal to Spain (D2). After closing it, we have a ready formula (Fig. 6).
=FILTER($B$2:$B$14,A$2:$A$14,=D2)
Fig. 6 FILTER function
We can see that Spain has only one representative, but Poland has got three (Fig. 7).
Fig. 7 FILTER function results
That’s why we have to put our FILTER function results into the TEXTJOIN function. And, once again, we have to write a comma and a space in double quotes. Then, we have to decide that we want to ignore empty cells and close the whole formula (Fig. 8)
=TEXTJOIN(“, “,1,FILTER($B$2:$B$14,A$2:$A$14=D2))
Fig. 8 TEXTJOIN with FILTER function
We can see that the results in the first solution and in the second solution are the same (Fig. 9)
