Problem With Timestamp past 12 midnight to the next day.

[Guzzlr]

Hello All,
I'm having an issue with my 2nd shift time of 4:00pm to 3:00am the next day. The problem occurs, when I cross over midnight, and the date has changed.
It's beginning to annoy me.

'Add 1st Start/End
Range("A1").Select
    ActiveCell.FormulaR1C1 = "1st Shift Start"
Range("B1").Select
    ActiveCell.FormulaR1C1 = "1st Shift End"
Range("A2").Select
    Selection.NumberFormat = "h:mm:ss"
    ActiveCell.FormulaR1C1 = "5:00:00 AM"     '1st Shift Start
Range("B2").Select
    Selection.NumberFormat = "h:mm:ss"
    ActiveCell.FormulaR1C1 = "16:00:00 PM"     '1st Shift End

'Add 2nd Shift Start/End
Range("D1").Select
    ActiveCell.FormulaR1C1 = "2nd Shift Start"
Range("E1").Select
    ActiveCell.FormulaR1C1 = "2nd Shift End"
Range("D2").Select
    Selection.NumberFormat = "h:mm:ss"
    ActiveCell.FormulaR1C1 = "16:30:00 PM"     '2nd Shift Start
Range("E2").Select
    Selection.NumberFormat = "[h]:mm:ss"
    ActiveCell.FormulaR1C1 = "28:00:00 AM"      '2nd Shift End
    

'Add 1st Shift Actual Wt.
Range("N3").Select

    ActiveCell.FormulaR1C1 = "1st Shift Actual Wt."
     Range("N4").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(RC2>=R2C1,RC2<=R2C2),RC7,"""")"
        Selection.AutoFill Destination:=Range("N4:N" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
    Columns("N:N").NumberFormat = "0.00"

    
    
'Add 2nd Shift Actual Wt.
Range("O3").Select

    ActiveCell.FormulaR1C1 = "2nd Shift Actual Wt."
     Range("O4").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(RC2>=R2C4,RC2<=R2C5),RC7,"""")"
        Selection.AutoFill Destination:=Range("O4:O" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
    Columns("O:O").NumberFormat = "0.00"

My 1st shift code works fine, because it stays on the same date.
The 2nd shift code works fine, until it crosses over to the next day, and I'm not really seeing why. For example 01:00:00 <= 03:00:00. however it is not picking it up, because it moves to the next date range.
I used 28:00:00 to try and trick it to think that is 4:00am...it didn't work
How do I get around this? Is there a TimeSerial I can use?
Thanks for the the help

 

[Micron]

That doesn't look anything like the data in post 7. This returns true in VBA
MsgBox #1/3/2022 9:44:20 PM# >= #1/3/2022 5:00:00 PM# And #1/3/2022 5:00:00 PM# <= #1/4/2022 5:00:00 AM#
I'd swap the date values for cell references, I guess.

 

[Guzzlr]

i'll work with a precision of 1 second and for every possible time in D

This is exactly what I did, instead of 30 minutes, I incremented by 1 second. All is working.
Thank you very much for the help!

 

[Guzzlr]

Well, I hate to come back to this but it is not working, it is simply copying all numbers from column N, which is 1st shift weight.
This is the "real Data Set"

My Code I'm using is:

'Add 2nd Shift Actual Wt.
Range("O3").Select
ActiveCell.FormulaR1C1 = "2nd Shift Actual Wt."
Range("O4").Select
ActiveCell.Formula = "=IF(OR($D$2=$B4,$E$2=$B4),$G4, IF(MEDIAN($D$2,$E$2,$G4)=$G4,IF($D$2<$E$2,$G4,""--""),IF($E$2<$D$2,$G4,""-"")))"
Selection.AutoFill Destination:=Range("O4:O" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

What Have I done wrong?
I simply changed the working code in post 19 to match the different columns above.
Do you see a problem?
Thank you

 

[Guzzlr]

Well, I hate to come back to this but it is not working, it is simply copying all numbers from column N, which is 1st shift weight.
This is the "real Data Set"

View attachment 55981

My Code I'm using is:

'Add 2nd Shift Actual Wt.
Range("O3").Select
ActiveCell.FormulaR1C1 = "2nd Shift Actual Wt."
Range("O4").Select
ActiveCell.Formula = "=IF(OR($D$2=$B4,$E$2=$B4),$G4, IF(MEDIAN($D$2,$E$2,$G4)=$G4,IF($D$2<$E$2,$G4,""--""),IF($E$2<$D$2,$G4,""-"")))"
Selection.AutoFill Destination:=Range("O4:O" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

What Have I done wrong?
I simply changed the working code in post 19 to match the different columns above.
Do you see a problem?
Thank you

I believe I have found my mistake...In the MEDIAN formula, it should be MEDIAN($D2$,$E$4,$D4)=$D4. Not $G4
Thank you