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]

Coming from Access I can say that trying to use only clock values (times with no date), vba will default to something like 01/01/1900 (for pc's) for the date. You probably need to treat it as 01/01/2022 04:00:00 PM vs 01/02/2022 03:00:00 AM.

Sometimes there are other issues when trying to use a span of date/time values, but I don't know if they are unique to Access. In case it matters here for you:
If you use >= [some date] AND <= [another date] (or the BETWEEN operator) and the date data includes time values but time is not factored into the end date criteria, you will retrieve data up to midnight of the 2nd date. In other words, anything that happened at 01:00:00 AM on the end date won't be retrieved. In fact, nothing after 1 second after midnight. There are a couple of work-arounds if this situation will apply to you.

 

[Guzzlr]

Coming from Access I can say that trying to use only clock values (times with no date), vba will default to something like 01/01/1900 (for pc's) for the date. You probably need to treat it as 01/01/2022 04:00:00 PM vs 01/02/2022 03:00:00 AM.

Sometimes there are other issues when trying to use a span of date/time values, but I don't know if they are unique to Access. In case it matters here for you:
If you use >= [some date] AND <= [another date] (or the BETWEEN operator) and the date data includes time values but time is not factored into the end date criteria, you will retrieve data up to midnight of the 2nd date. In other words, anything that happened at 01:00:00 AM on the end date won't be retrieved. In fact, nothing after 1 second after midnight. There are a couple of work-arounds if this situation will apply to you.

So you are suggesting a BETWEEN of times?
BETWEEN 16:00:00 AND 03:00:00
not really following you here, sorry

 

[Micron]

No, I'm saying that time periods that involve different dates (days) require that the day dates be included. Does that clarify? Again, I'm coming at this from my Access expertise, but I believe that doesn't really matter.

 

[BSALV]

for such overnight problems, if your endtime is smaller then your starttime, add 1.
Only valid if the difference between both is less then 24 hours !

    Range("N2").FormulaR1C1 = "=IF(COUNT(RC1:RC2)=2,RC2-RC1+(RC2<RC1),""--"")"
    Range("O2").FormulaR1C1 = "=IF(COUNT(RC4:RC5)=2,RC5-RC4+(RC5<RC4),""--"")"

 

[Guzzlr]

for such overnight problems, if your endtime is smaller then your starttime, add 1.
Only valid if the difference between both is less then 24 hours !

    Range("N2").FormulaR1C1 = "=IF(COUNT(RC1:RC2)=2,RC2-RC1+(RC2<RC1),""--"")"
    Range("O2").FormulaR1C1 = "=IF(COUNT(RC4:RC5)=2,RC5-RC4+(RC5<RC4),""--"")"

Thank you BSALV
I do understand the concept of adding 1.
However, I am having trouble with the syntax to add it to my existing code.
Can I get some direction here please?
Thank you for your time.

Range("O4").Select
ActiveCell.Formula = "=IF(AND($B4>=$D$2,$B4<=$E$2),$G4,"""")"

 

[Guzzlr]

Thank you BSALV
I do understand the concept of adding 1.
However, I am having trouble with the syntax to add it to my existing code.
Can I get some direction here please?
Thank you for your time.

Range("O4").Select
ActiveCell.Formula = "=IF(AND($B4>=$D$2,$B4<=$E$2),$G4,"""")"

If I manually add "+1" to the time (as below) I do get the output of the formula in column O (1483.15 & 20.00)
Just having problems adding it to my code above, so it doesn't add "+1" to all the time cells
Thanks for the help

1/3/2022	9:44:20 PM	1123	1123-RA-0-BC	SKB8C2FB	No Tag Found-	22.25	9	247%	0.6741533	Read Error	13.25	1		22.25
1/4/2022	12:35:25 AM+1	1259	1259-0-AB	SKB8C33S	1259-E	1483.15	12	12360%	0.6612344	Erica Garcia	1471.15	1		1483.15
1/4/2022	12:36:43 AM+1	1957	1957-LA-0-AA	SKB8C467	1956LA-B	20.00	12	167%	0.6249491	Erica Garcia	8.00	1		20.00

 

[BSALV]

with your instant translator

     Range("N2").FormulaR1C1 = "=IF(COUNT(RC1:RC2)=2,RC2-RC1+(RC2<RC1),""--"")"
     Range("O2").FormulaR1C1 = "=IF(COUNT(RC4:RC5)=2,RC5-RC4+(RC5<RC4),""--"")"
     Range("N3").Value = "'" & Range("N2").Formula
     Range("O3").Value = "'" & Range("O2").Formula

i added an image, xl2bb wasn't available, don't know why.

 

[Guzzlr]

with your instant translator

     Range("N2").FormulaR1C1 = "=IF(COUNT(RC1:RC2)=2,RC2-RC1+(RC2<RC1),""--"")"
     Range("O2").FormulaR1C1 = "=IF(COUNT(RC4:RC5)=2,RC5-RC4+(RC5<RC4),""--"")"
     Range("N3").Value = "'" & Range("N2").Formula
     Range("O3").Value = "'" & Range("O2").Formula

i added an image, xl2bb wasn't available, don't know why.

I'm only going to be using one column, which is the 2nd shift column. this is the one that is giving me a hard time as it is the one that crosses over midnight to the next day. Therefore, can I only use your formula of Column O and alleviate Column N?
Thanks for the help

 

[Guzzlr]

So I've made another excel sheet so not to mess up my "real sheet"
Below is the new sheet:
Start is on E2 and End is on F2
As you can see, the formula is working until midnight. However, if I change the End time to anything in the morning, say 04:00:00 AM, then all formula numbers in Column E disappear. Which makes sense as the time no longer falls between anymore.

And, I'm trying to use your code with out any luck.... When I try to run with your lines in, everything blanks out, and the formula is placed in E2:E3.

Sub RunProgram()


Sheets("VBACodeWay").Select

Range("E5").Select
ActiveCell.Formula = "=IF(AND($C5>=$E$3,$C5<=$F$3),$D5,"""")"
Selection.AutoFill Destination:=Range("E5:E" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("E2").FormulaR1C1 = "=IF(COUNT(RC5:RC6)=2,RC6-RC5+(RC6<RC5),""--"")"
Range("E3").Value = "'" & Range("E2").Formula

Range("A1").Select

End Sub