when the date changes but the 'date-code' should not...

[QMDirk]

Hi everyone. I have a situation (doesn't everyone in here? lol). I have a form that displays a lot-code date for a manufacturing process. In my plant, on 2nd shift, it is our policy that when the time passes midnight, the code does not change dates but keeps the same date. So I need to write a line of code that states that any production time between 12:01 am and 05:59 am will have yesterdays (Julian) date. The code looks this: "5401010420" where "104" represents the Julian Calendar date. The part that I don't understand is how to format the "time" in VBA in order to search for production between those 2 times. It must be "After midnight, BUT before 05:59am, and I have no way to specify the date part, only the time part. Any thoughts? On the form, there is a text box that displays the current date and one that displays the current time. I may be able to reference-off one of those?

 

[Dreamweaver]

Not on my pc at moment but I use someting like format(date,"ddmmyyyy") & format(timer, ("000000")
If you just want the current date and time you can use now() and format that how you want

 

[QMDirk]

What I'm looking for is something like this:
If Time( ) > 12:00:00 AM AND If Time< 05:59:00 AM Then... 'this could easily just reference the value of another textbox that displays the time, or read the system clock, whichever). I've the code to display the actual "DATE_CODE", it's the If-Then statement I'm having difficulty figuring out. Thanks.

 

[Dreamweaver]

Not done that but you could format both sides but I would be asking myself why is the date time not updating which you seem to suggest in your first post

 

[Gasman]

I believe you would use # delimiters.
I would also use 24 hour format?

? time()
12:24:41 
? time() < #17:00:00#
True

 

[isladogs]

06:00:00 is 0.25 of a day and 05:59:00 is slightly less than that (0.249305555555...)

I suggest you use the following

If CDbl(Time()) Between 0 And 0.24931 Then

 

[QMDirk]

Here's the actual VBA that displays the date code in a textbox . The bold line displays the code. The other textboxes below represent the expiration date, but they are not an issue here.
Private Sub Form_Load()
Dim PN As String 'Plant Number "5401"
Dim YR As String 'last digit of the year
Dim JD As String 'julian day section
Dim DC As String ' single digit to represent the decade
Dim TD As String 'today's date and time
PN = "5401"
YR = "0"
JD = Format(DatePart("y", Date), "000")
DC = "2"

On Error Resume Next 'on the form, "txtJulDate" is an UNBOUND textbox
'that displays the string, the form is read-only
Me.txtJulDate = PN & YR & JD & DC & "0" 'my thought here was to subtract "1" from the variable "JD" so I could use the System Clock as the reference

Me.txt270 = Date + 270
Me.txt360 = Date + 360
Me.txt480 = Date + 480
Me.txt560 = Date + 560
Me.txt720 = Date + 720
Me.txt730 = Date + 730
End Sub

 

[QMDirk]

06:00:00 is 0.25 of a day and 05:59:00 is slightly less than that (0.249305555555...)

I suggest you use the following

If CDbl(Time()) Between 0 And 0.24931 Then

Thanks. Does it have to be "CDbl" specifically? Or did you use that as an example of a variable name?

 

[QMDirk]

Not done that but you could format both sides but I would be asking myself why is the date time not updating which you seem to suggest in your first post

I meant that it is not our plant's policy to change the date code for production hours between midnight and 6am. We run 2 10-hour shifts and and second shift runs until 3 am, but 1st shift comes in at 6am. sorry for the confusion.

 

[isladogs]

Thanks. Does it have to be "CDbl" specifically? Or did you use that as an example of a variable name?

Dates and times are stored as Double numbers and displayed in your default date/time format or any other format you specify
The CDbl function converts it back to a double number

For example... from the immediate window in the VBE

?Date
12/04/2020
?CDbl(#12/04/2020#)
44169

?Now
12/04/2020 14:25:15
?Cdbl(#12/04/2020 14:25:15#)
 44169.6008680556

?Time
14:26:11
?CDbl(#14:26:11#)
 0.601516203703704

 

[QMDirk]

Dates and times are stored as Double numbers and displayed in your default date/time format or any other format you specify
The CDbl function converts it back to a double number

For example... from the immediate window in the VBE

?Date
12/04/2020
?CDbl(#12/04/2020#)
44169

?Now
12/04/2020 14:25:15
?Cdbl(#12/04/2020 14:25:15#)
44169.6008680556

?Time
14:26:11
?CDbl(#14:26:11#)
0.601516203703704

Got it. Thanks!

 

[QMDirk]

Thanks for everyone's suggestions. This worked:

On Error Resume Next 'on the form, "txtJulDate" is an UNBOUND textbox that displays the string, the form is read-only
If tCDblTime.Value <= 0.249305 Then ' subtract "1" from the variable "JD"
Me.txtJulDate = PN & YR & JD - 1 & DC & "0"
Else: Me.txtJulDate = PN & YR & JD & DC & "0"
End If

I created a textbox named "tCDblTime" and set its source to "=CDbl(Time())", then just compared the values of the 2 textboxes. When I changed the system clock to 3am, the value in tCDblTime dropped below 00.249305, and the readout turned from 5401010320 to 5401010220.