Determine if a given time is between start and end time

5
votes

I got two columns with Start and End times of shifts, I need a formula that returns if NOW() is on shift/off shift (eg. TRUE/FALSE)

enter image description here

Answers I found only using MEDIAN, AND+IF did not work as shift can start evening and finish day time. Anyone got an elegant solution for this?

Bare in mind cases when it is after midnight.

5
vbaexcelexcel-formula

5 Answers

2
votes

Since you tagged as & , you can use a UDF:

Public Function onShift(rngStart As Date, rngEnd As Date) As Boolean
    Application.Volatile
    If rngStart > rngEnd Then
        If Time < rngEnd Then 'After midnight & in-shift
            rngStart = Date + rngStart - 1
            rngEnd = Date + rngEnd
        Else
            rngStart = Date + rngStart
            rngEnd = Date + rngEnd + 1
        End If
        If Now >= rngStart And Now <= rngEnd Then onShift = True
    Else
        If Time >= rngStart And Time <= rngEnd Then onShift = True
    End If
End Function

enter image description here

But I would stick with worksheet functions as provided by Scott's answer.

The benefit of using a UDF however is that you are able to create easy-to-remember function names that does exactly what you need it to do.

5
votes

Use:

=MEDIAN(MOD($C$1,1),-AND(A2>B2,B2>MOD($C$1,1))+A2,AND(A2>B2,B2<MOD($C$1,1))+B2)=MOD($C$1,1)

You can replace all the $C$1 references with NOW(), or just put =NOW() in C1

enter image description here

enter image description here

enter image description here

3
votes

Assuming you have =NOW() in C1 (which will include date and time) you can use this formula:

=(MOD(C$1,1)<B2)+(MOD(C$1,1)>A2)+(B2<A2)=2

This works because if the time in B2 is > the time in A2 [shift is on one day] then the first two conditions need to be TRUE....but if the time in B2 is < A2 [shift cuts across two days] then only one of those conditions needs to be TRUE (or can be TRUE). Either way 2 of the conditions need to be TRUE

If you use this formula in C1 which will return the current time without date

=NOW()-TODAY()

...then above formula can be shortened to this:

=(C$1<B2)+(C$1>A2)+(B2<A2)=2

see screenshot below

enter image description here

2
votes

Try:

=OR(AND(B2-A2<0,OR($C$1<=B2,$C$1-A2>=0)),AND($C$1>=A2,$C$1<=B2))

You can replace $C$1 with TEXT(NOW(),"hh:mm") to evaluate the current time.

2
votes

Considering all compared values are in TIME format, you can try:

=MEDIAN(A2,IF(B2<A2,B2+1,B2),IF(C$1<A2,C$1+1,C$1))=IF(C$1<A2,C$1+1,C$1)

If however you need to compare it with current time using NOW(), you have to strip the time out of it like:

=NOW()-INT(NOW()) '/* this goes to C$1 */

I saw how well Scott did it and the logic is way too high (at least for me) so I decided to make something where the logic is pretty straight forward.