PDA

View Full Version : [SOLVED] Determing if a time of day falls between time of shift



boccuz
02-03-2021, 02:06 PM
Hello,

I am trying to calculate if a the time of a call (Column "E") falls between the hours of the day shift or the night shift. If the time falls between the hours of the day shift, I would like to place the number 1 in column "H" or in column "K" if it falls within the hours of the night shift. Our night shift starts at 23:00 hours the previous day and ends at 06:00 hours. For clarification, If I am scheduled to work the night shift on Wednesday, I would report for duty on Tuesday evening at 23:00hrs and work until 06:00 hours on Wednesday. The Wednesday day shift would be 06:00 to 23:00 hours. I tried calculating the time using columns "F". "G", "I", and "J", but i am not sure I really need these columns. looking for the simplest way possible. Thank you for your help.

Paul_Hossler
02-03-2021, 03:52 PM
SOme of the worksheet formula gurus may be able to come up with formulas, but since you already have macros and I personally prefer UDFs for complicated logic, I did a single User Defined Function with a switch to test for Day or Night shift




Option Explicit


Function Shift(T As String, DayShift As Boolean) As Long
If DayShift Then
If "06:00" <= T And T < "22:59" Then
Shift = 1
Else
Shift = 0
End If

Else
If ("23:00" <= T And T < "23:59") Or ("00:00" <= T) And (T < "06:00") Then
Shift = 1
Else
Shift = 0
End If
End If
End Function

p45cal
02-03-2021, 04:46 PM
in column H:
=--(MEDIAN(TIMEVALUE(E2),F2,G2)=TIMEVALUE(E2))
in column K:
=--NOT(MEDIAN(TIMEVALUE(E2),I2,J2)=TIMEVALUE(E2))
but make sure the times in column I and J are night shift times, at the moment they're the same as the day shift.

It would simplify things if your formula in column E was:
=TIMEVALUE(MID(A2,12,5))
then the H and K column formulae could be:
=--(MEDIAN(E2,F2,G2)=E2)
and
=--NOT(MEDIAN(E2,F2,G2)=E2)

Note, the night formula assumes that there are only 2 shifts per day and that they're simply not the day shift times.

boccuz
02-04-2021, 07:17 AM
Paul, Thanks. This is excellent. I truly appreciate your help.

boccuz
02-08-2021, 01:21 PM
Sorry it took so long to close this thread, but wanted to make sure everything worked as expected before I did. Thank you to Paul Hossler and p45cal for their help. I tried both methods but the macro worked best for what I needed to accomplish. I really appreciate the time you took to help me out.