Convert Date/Time into Shift

Question

Hello,

I have a completion date field in Date Time format (12/8/2010 22:25) and would like to create a return field that convert this to which shift (1^st, 2^nd or 3^rd) they are completed on. The break down is this.

1^st shift = 6:00 AM – 2:30 PM

2^nd shift = 2:31 PM – 11:00 PM

3^rd shift = 11:01 PM – 5:59 AM

What would like the formula to calculate this? Thanks

Answer 1

I think I'm receiving telepathic communications from you, Marsh.  Completely out of the blue it suddenly occurred to me this morning that I'd got that parenthesis in the wrong place.  It happened because I first forgot to include the TimeValue function, then added it later.

---

Ken Sheridan, Stafford, England

Answer 2

Well said Ken.

There's a little typo with the opening ( in the wrong place:

   ON (TimeValue([Timesheet].[CompletionDate])

      BETWEEN [Shifts].[StartTime] AND [Shifts].[EndTime])

Answer 3

I agree 110% with Marshall that the shifts should be stored as rows in a table.  The shift names and times are data , and data should only be stored as values in rows in tables, not hard-coded in code or an SQL statement.  This was in fact Codd's Rule #1, the Information Rule, when he first put forward the database relational model in 1970 and was later expressed by Date as the Information Principle:

'The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in rows in tables'.

C J Date - Introduction to Database Systems; 7th Edition; 2000

You can then return the shift for any time in a query like so:

SELECT [CompletionDate], [Shift]

FROM [Timesheet] INNER JOIN [Shifts]

ON TimeValue(([Timesheet].[CompletionDate]) BETWEEN

[Shifts].[StartTime] AND [Shifts].[EndTime]);

Note that the join condition must be enclosed in parentheses when using a BETWEEN....AND operation in the JOIN clause.

A practical implication of this is of course that, in the event of the shift times changing, it's merely a case of updating the rows in the Shifts table.  However, this will affect existing timesheet records as well as new ones, so if the historical shift pattern needs to be preserved in the timesheet records the Shift value obtained from the Shifts table will need to be assigned to a Shift column in the timesheet table rather than merely 'pulled' in from it in a query.  This would be done in the timesheet data input form in the AfterUpdate event procedure of the CompletionDate control with code like so:

Dim strCriteria As String

strCriteria = "#" & Format(Me.[CompletionDate],"hh:nn:ss") & _

    "# Between [StartTime] And [EndTime]"

Me.[Shift] = DLookup("Shift", "Shifts", strCriteria)

---

Ken Sheridan, Stafford, England

Answer 4

One way could be like:

Switch(TimeValue(CompDate) > #11:00# Or TimeValue(CompDate) < #6:00#,"3rd", TimeValue(CompDate) <= #14:30#,"1st", TimeValue(CompDate) <= #23:00#,"2nd")

It would be better to avoid specifying the shift names and times in the query and use an auxillary table with the shift names and shift start and end times.  Then you could use an non equi join in an SQL view only query, but that may be overkill for your question.

Answer 5

Try this in a query:

Shift: IIf(TimeSerial(Hour([CompletionTime]-0.25),Minute([CompletionTime]),0)<=#08:30:00#,1,IIf(TimeSerial(Hour([CompletionTime]-0.25),Minute([CompletionTime]),0)<=#17:00:00#,2,3))