Solved Calculate correct shift worked

oxicottin · Sep 18, 2023

I need help with a function to calculate what shift the “DelayStart” time is on. I made this function below but I'm not sure if it's calculating the shift correctly OR if there is a better way?

Shift 1 the “DelayStart” is 4:45am – 1550pm
Shift 2 the “DelayStart” is 1550pm – 1:45am
Shift 3 the “DelayStart” is 1:45am – 4:45am

Code:

Public Function GetShiftForRecord(DelayStart as Variant)
h = DatePart(“h”, DelayStart)
m = DatePart(“m”, DelayStart)

If h >= 2 And h < 5 And IIf(h = 4, IIf(m <= 59, True, False), True) Then
GetShiftForRecord = 3
ElseIf h >= 5 And h < 16 And IIf(h = 15, IIf(m <= 49, True, False), True) Then
GetShiftForRecord = 1
Else
GetShiftForRecord = 2

End Function

CJ_London · Sep 19, 2023

easier just use the number of minutes

1:45am is 105 mins
4:45am is 285 mins
etc

oxicottin · Sep 19, 2023

@CJ_London the "delaystart" is a date/time captured by a PLC i need to utilize the function in a query and throughout the code to capture the shift I'm not following what your suggesting.

MajP · Sep 19, 2023

Code:

Public Function GetShiftForRecord(DelayStart As Date) As Integer
 'Shift 1 the “DelayStart” is 4:45am – 1550pm
 'Shift 2 the “DelayStart” is 1550pm – 1:45am
 'Shift 3 the “DelayStart” is 1:45am – 4:45am
  Dim shift As Integer
  Select Case DelayStart

  Case #4:45:00 AM# To #3:50:00 PM#
    shift = 1
  Case #3:50:00 PM# To #11:59:59 PM#
    shift = 2
  Case #12:00:00 AM# To #1:45:00 AM#
    shift = 2
  Case #1:45:00 AM# To #4:45:00 AM#
    shift = 3
  End Select
  GetShiftForRecord = shift
End Function

The TO is a greater than to less than or equal. Adjust as necessary for the boundary
Query1 Query1

fldTime	Shift
12:00:00 AM	2
12:15:00 AM	2
12:30:00 AM	2
12:45:00 AM	2
1:00:00 AM	2
1:15:00 AM	2
1:30:00 AM	2
1:44:59 AM	2
1:45:00 AM	2
1:45:01 AM	3
2:30:00 AM	3
2:45:00 AM	3
3:00:00 AM	3
3:15:00 AM	3
3:30:00 AM	3
3:45:00 AM	3
4:00:00 AM	3
4:15:00 AM	3
4:30:00 AM	3
4:45:00 AM	3
4:45:01 AM	1
5:15:00 AM	1
5:30:00 AM	1
5:45:00 AM	1
6:00:00 AM	1
6:15:00 AM	1
6:30:00 AM	1
6:45:00 AM	1
7:00:00 AM	1
7:15:00 AM	1
7:30:00 AM	1
7:45:00 AM	1
8:00:00 AM	1
8:15:00 AM	1
8:30:00 AM	1
8:45:00 AM	1
9:00:00 AM	1
9:15:00 AM	1
9:30:00 AM	1
9:45:00 AM	1
10:00:00 AM	1
10:15:00 AM	1
10:30:00 AM	1
10:45:00 AM	1
11:00:00 AM	1
11:15:00 AM	1
11:30:00 AM	1
11:45:00 AM	1
12:00:00 PM	1
12:15:00 PM	1
12:30:00 PM	1
12:45:00 PM	1
1:00:00 PM	1
1:15:00 PM	1
1:30:00 PM	1
1:45:00 PM	1
2:00:00 PM	1
2:15:00 PM	1
2:30:00 PM	1
2:45:00 PM	1
3:00:00 PM	1
3:15:00 PM	1
3:30:00 PM	1
3:45:00 PM	1
3:49:00 PM	1
3:50:00 PM	1
3:50:01 PM	2
4:45:00 PM	2
5:00:00 PM	2
5:15:00 PM	2
5:30:00 PM	2
5:45:00 PM	2
6:00:00 PM	2
6:15:00 PM	2
6:30:00 PM	2
6:45:00 PM	2
7:00:00 PM	2
7:15:00 PM	2
7:30:00 PM	2
7:45:00 PM	2
8:00:00 PM	2
8:15:00 PM	2
8:30:00 PM	2
8:45:00 PM	2
9:00:00 PM	2
9:15:00 PM	2
9:30:00 PM	2
9:45:00 PM	2
10:00:00 PM	2
10:15:00 PM	2
10:30:00 PM	2
10:45:00 PM	2
11:00:00 PM	2
11:15:00 PM	2
11:30:00 PM	2
11:45:00 PM	2
11:59:59 PM	2

oxicottin · Sep 19, 2023

@MajP does it matter if the PLC is collecting time as military time (12)? I tried changing the code to #15:50:00# and it reverts back to #3:50:00 PM#

MajP · Sep 19, 2023

Should not matter. Time is not saved in any format, only displayed that way. Time is stored as a decimal value which is a fraction of a day. So 6 am is stored as .25. 12pm is .5 of day. 11:59 pm is like .99 something.

MajP · Sep 19, 2023

To see how date and time is saved you can convert to double

Code:

Public Sub TestDate()
  Debug.Print Now
  Debug.Print CDbl(Now)
  Debug.Print CLng(Now)
  Debug.Print CDbl(Now - Int(Now))
End Sub

Code:

9/19/2023 9:09:28 AM
45188.3815740741
45188
0.381574074075615

This is how all dates are stored. An integer portion for the date and a decimal portion for the time ( 45188.3815740741). Regardless of what you see.

The integer portion is how many days have passed since the base date (12/31/1899). 45188 days have passed since the Microsoft Chosen base date.

The time portion is fraction of a day.
9:09:28 AM is 0.381574074075615 of a day.
This makes sense since it is between 6 am and 12pm. 6 am is .25 of a day and 12pm is .5 of a day.

The BIG question is if your dates are saved as dates. If they are strings then you have to do some conversion likely.

oxicottin · Sep 20, 2023

@MajP I tried your example and ran the database to collect current data from PLC and it gave me a Zero entry for my shift. However, I tried the code below and it worked, and I don't know why yours didn't? in my immed window it printed the t "DelayStart" like 09/19/2023 19:29:00 is it because I used the TimeValue?

Code:

Public Function getShiftForRecord(DelayStart as Variant)

t = TimeValue(DelayStart)

'Debug.print t

Dim t as variant

if t >= #1:46:00 AM# And t <= #5:59:59 AM# Then
    getShiftForRecord = 3
Elseif t >= #6:00:00 AM# And t <= #3:50:00 PM# Then
    getShiftForRecord = 1
Else
    getShiftForRecord =2
End if
End Function

MajP · Sep 20, 2023

is it because I used the TimeValue?

Yes very likely. Mistake on my end for not accounting for you potentially passing in a date part. As I mentioned a datetime value has both a date part and a time part. I did not account for you passing in a date part. Sometimes a date part is there even though you do not see it because you format only to see the time. The integer part needs to be zero in my example.

In my example I should have likely had

Code:

Select Case timevalue(DelayStart)
or
DelayStart = DelayStart - int(DelayStart)  to remove any possible day component

If you are passing in a date part in other words the integer portion not zero then my code fails because every value would be greater then the cases.

This can get really confusing, but remember EVERY date you look at in a table, form, query, or report is a formatted representation of what is stored. What you see may not be exactly what is stored. You may have a date part and a time part but display just the time or just the date.
I have seen people lose their minds because they see a table with a bunch of dates including 9/19/2023. They write a query where SomeField = #9/19/2023# and no records are returned. That is because the values in the table are formatted without the date part and there is no record = exactly to 9/19/2023.

oxicottin · Sep 20, 2023

@MajP I used your example above with your previous code and it works now.... Thanks!

Solved Calculate correct shift worked

oxicottin

Learning by pecking away....

CJ_London

Super Moderator

oxicottin

Learning by pecking away....

MajP

You've got your good things, and you've got mine.

oxicottin

Learning by pecking away....

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

oxicottin

Learning by pecking away....

MajP

You've got your good things, and you've got mine.

oxicottin

Learning by pecking away....

Similar threads

Users who are viewing this thread