Is this Time between Working Hours

How can we check whether the giv­en time is between the work­ing hours or not?

In our case, work starts at 9:30 AM, and ends at 5:30 PM. Our giv­en time, which is 6:47 isn’t between the work­ing ours. When we change it, we can see that the col­or changed into red and ‘No’ turned into ‘Yes’, which means that now the time is between the work­ing ours (Fig. 1)

How­ev­er, when we go to ear­li­er hours we can see that the details changed (Fig. 2)

How can we check our time? The sim­plest solu­tion is using the MEDIAN func­tion that returns the val­ue in the mid­dle. Since all our times are in one row, we can just select the range (Fig. 3)

=MEDIAN(B2:D2)

If our data is in sep­a­rate cells, we can just add those cells one by one (Fig. 4)

=MEDIAN(B2,C2,D2)

And just like that we receive the val­ue in the mid­dle. If this val­ue is equal to the giv­en time, it means it’s between the work­ing hours. What’s curi­ous about our medi­an is the fact that it won’t cross our thresh­olds, i.e. our start­ing time and fin­ish­ing time. It means that the small­est val­ue our MEDIAN func­tion can show is the start­ing hour, and the largest is the fin­ish­ing hour (Fig. 5)

Now, we have to check whether our time is between the work­ing hours. We have to com­pare the func­tion to the giv­en time (Fig. 6)

=MEDIAN(B2,C2,D2)=B2

As we can see, our log­i­cal test returned FALSE (Fig. 7)

If the log­i­cal test returns TRUE, it means that the giv­en time is between the work­ing ours (Fig. 8)

The answers we have are log­i­cal. Let’s make them more human like by adding the IF func­tion to our MEDIAN log­i­cal test and some sim­ple text like ‘Yes’ and ‘No’ (Fig. 9)

=IF(MEDIAN(B2,C2,D2)=B2,“Yes”,“No”)

We can see that we can check whether our giv­en time is between the work­ing ours or no (Fig. 10)

https://www.youtube.com/watch?v=IVZIY6-78sc