Help with time and date status... everyone's favourite!
I have a bit of a challenge I hope someone can help me with. I have a field called DUTY_STATUS which I want to automatically indicate whether a unit or crew would be "on duty" or "off duty" depending on time of day. If I was using strict timestamps associated to start and end dates this would be fairly simple, however I want to keep it open ended so it will calculate on any day at any time. I know that seems a little loosey-goosey but in this context it works.
To work with, I have SHIFT_START (a time field), SHIFT_END (a time field) and CURRENT_TIME (which is an expression of Get (CurrentHostTimeStamp).
For the end result, I would like DUTY_STATUS to show as "on duty" if CURRENT_TIME is between SHIFT_START and SHIFT_END, and "off duty" if CURRENT_TIME is anything outside of those two times. Where this gets tricky, is when some of the SHIFT_END times will be after midnight.
I feel like this is something easy that I'm missing. I searched all the forums for something similar but I can't find anything and I'm having a hard time making it work.
Any help would be appreciated........ Cheers.