Determine if a given time is between start and end time

Question

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)

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.

Answer 1

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

Answer 2

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

Answer 3

Since you tagged as vba & excel-vba, 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

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.