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

 

[NoSparks]

@Guzzlr
I find your explanation of things a little confusing.
With the picture in post 10,
if it is reasonable to think that all the times in column C are end times
and the time in E3 is to be the start time for all of them
then I would try this. It puts the shift start and end in E3 and F3
and the formula for hours worked in column E from row 5 down

Sub RunProgram()

Sheets("VBACodeWay").Select

' Add shift start info
Range("E2").Value = "Start"
Range("E3").Value = TimeSerial(17, 0, 0)

' Add shift end info
Range("F2").Value = "End"
Range("F3").Value = TimeSerial(4, 0, 0)

' Add the hours worked formula where required
With Range("E5:E" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(OR(R3C5="""",RC3=""""),""--"",(RC3-R3C5+(RC3<R3C5))*24)"
    .NumberFormat = "0.00"
End With

' Position the cursor
Range("A1").Select

End Sub

If that's wrong or you still don't follow then you should consider sharing a desensitized workbook of what you are actually working with.

 

[BSALV]

until now we only used the time, not the date.
In E3, you also have no date included.
I used E3 as starttime and the C-column as endtime

Range(Range("E5"), Range("E" & Range("C" & Rows.Count).End(xlUp).Row)).FormulaR1C1 = "=IF(COUNT(R3C5,RC3)=2,+RC3-R3C5+(RC3<R3C5),""-"")"

this is a more condensed way of writing, but does the same.
The part before the equation-sign deteminates the range, starting in E5 and goes to the E-cell of the last used row of C, the 2nd part is the formula i made for E5, with the right $-signs,but now for all the rest

Map1
	C	D	E
1
2			start
3			5:00 PM
4	time	number	-
5	1:00 PM		20:00
6	1:30 PM		20:30
7	2:00 PM		21:00
8	2:30 PM		21:30
9	3:00 PM		22:00
10	3:30 PM		22:30
11	4:00 PM		23:00
12	4:30 PM		23:30
13	5:00 PM		00:00
14	5:30 PM		00:30
15	6:00 PM		01:00
16	6:30 PM		01:30
17	7:00 PM		02:00
18	7:30 PM		02:30
19	8:00 PM		03:00
Blad1

Cell Formulas
Range	Formula
C6:C19	C6	=+C5+TIME(0,30,0)
E4:E19	E4	=IF(COUNT($E$3,$C4)=2,(+$C4-$E$3+($C4<$E$3)),"-")

 

[Guzzlr]

@Guzzlr
I find your explanation of things a little confusing.
With the picture in post 10,
if it is reasonable to think that all the times in column C are end times
and the time in E3 is to be the start time for all of them
then I would try this. It puts the shift start and end in E3 and F3
and the formula for hours worked in column E from row 5 down

Sub RunProgram()

Sheets("VBACodeWay").Select

' Add shift start info
Range("E2").Value = "Start"
Range("E3").Value = TimeSerial(17, 0, 0)

' Add shift end info
Range("F2").Value = "End"
Range("F3").Value = TimeSerial(4, 0, 0)

' Add the hours worked formula where required
With Range("E5:E" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(OR(R3C5="""",RC3=""""),""--"",(RC3-R3C5+(RC3<R3C5))*24)"
    .NumberFormat = "0.00"
End With

' Position the cursor
Range("A1").Select

End Sub

If that's wrong or you still don't follow then you should consider sharing a desensitized workbook of what you are actually working with.

So this I tried (code above), and unfortunately it filled all of column E.
is it missing:

ActiveCell.Formula = "=IF(AND($C5>=$E$3,$C5<=$F$3),$D5,"""")"

What I'm trying to accomplish, is if the times in Column C, fall between the range of start and finish (E3 and F3), then the number associated in column D, will install in Column E.
I can get it to work, up until midnight, when it crosses into the next day, then the program will no longer work. For example, Row 27 and 28, crosses over midnight to the next day. My program: 'ActiveCell.Formula = "=IF(AND($C5>=$E$3,$C5<=$F$3),$D5,"""")" will place the number in column D into column E, up to row 27, and no further.
Thanks for the help

 

[Guzzlr]

68.6 KB file on MEGA

mega.nz

65.8 KB file on MEGA

mega.nz

Here is a link to the file, I've never tried this before, so not sure if it will work.
The top file is my code and you can see it works, up unill midnight, then no longer works as it moves to the next day
The second file is with NoSparks code.
The two can be compared.
Please keep in mind, this is a small test file, with nicely spaced time stamps, in real life, the file will have many time stamps in the seconds range, different from the previous time stamp.
However, there will never be a duplicate time stamp on the same day.
My thinking is if code will work on this small file, it should also work on the "real file"

 

[BSALV]

Map1
	D	E	F	G	H
15			start
16			17:00	21:00
17	time	number
18	0:00	A	-
19	1:00	A	-
20	2:00	A	-
21	3:00	A	-
22	4:00	A	-
23	5:00	A	-
24	6:00	A	-
25	7:00	A	-
26	8:00	A	-
27	9:00	A	-
28	10:00	A	-
29	11:00	A	-
30	12:00	A	-
31	13:00	A	-
32	14:00	A	-
33	15:00	A	-
34	16:00	A	-
35	17:00	A	A
36	18:00	A	A
37	19:00	A	A
38	20:00	A	A
39	21:00	A	A
40	22:00	A	-
41	23:00	A	-
42	0:00	A	-
43
Blad1

Cell Formulas
Range	Formula
D19:D42	D19	=+D18+TIME(1,0,0)
F18:F42	F18	=IF(OR($F$16=D18,$G$16=D18),E18, IF(MEDIAN($F$16,$G$16,D18)=D18,IF($F$16<$G$16,E18,"--"),IF($G$16<$F$16,E18,"-")))

variant

Map1
	D	E	F	G
14
15			start
16			17:00	15:00
17	time	number
18	0:00	A	A
19	1:00	A	A
Blad1

Cell Formulas
Range	Formula
D19	D19	=+D18+TIME(1,0,0)
F18:F19	F18	=IF(($F$16=D18)+($G$16=D18)+(MEDIAN($F$16,$G$16,D18)=D18)*($F$16<$G$16)+(MEDIAN($F$16,$G$16,D18)<>D18)*($G$16<$F$16),E18,"-")

 

[Micron]

In post 7 (?) there are dates in one column and times in the next. I have to wonder what's wrong with what I suggested; i.e. why you cannot use DateDiff as long as you can string together those 2 columns as 1 date/time value? So comparing the 1st two rows:
Datediff("n",#1/3/2022 9:44:20 PM#,#1/4/2022 12:35:25 AM#)/60 = 2.85 hours. Perhaps you want to see hours and minutes, but that's another step.

Perhaps the end goal escapes me, but from what I recall reading here, the thread is about calculating the time span over midnight and not too much about what to do with the calculation.

 

[BSALV]

Micron's idea is even easier

Map2
	A	B	C	D	E
1				start
2				23/01/2022 17:00	24/01/2022 15:31
3		time	number
4	24/01/2022	0:00:00	A	A
5	24/01/2022	0:30:00	A	A
6	24/01/2022	1:00:00	A	A
7	24/01/2022	1:30:00	A	A
8	24/01/2022	2:00:00	A	A
9	24/01/2022	2:30:00	A	A
10	24/01/2022	3:00:00	A	A
11	24/01/2022	3:30:00	A	A
12	24/01/2022	4:00:00	A	A
13	24/01/2022	4:30:00	A	A
14	24/01/2022	5:00:00	A	A
15	24/01/2022	5:30:00	A	A
16	24/01/2022	6:00:00	A	A
17	24/01/2022	6:30:00	A	A
18	24/01/2022	7:00:00	A	A
19	24/01/2022	7:30:00	A	A
20	24/01/2022	8:00:00	A	A
21	24/01/2022	8:30:00	A	A
22	24/01/2022	9:00:00	A	A
23	24/01/2022	9:30:00	A	A
24	24/01/2022	10:00:00	A	A
25	24/01/2022	10:30:00	A	A
26	24/01/2022	11:00:00	A	A
27	24/01/2022	11:30:00	A	A
28	24/01/2022	12:00:00	A	A
29	24/01/2022	12:30:00	A	A
30	24/01/2022	13:00:00	A	A
31	24/01/2022	13:30:00	A	A
32	24/01/2022	14:00:00	A	A
33	24/01/2022	14:30:00	A	A
34	24/01/2022	15:00:00	A	A
35	24/01/2022	15:30:00	A	A
36	24/01/2022	16:00:00	A	-
37	24/01/2022	16:30:00	A	-
38	24/01/2022	17:00:00	A	-
39	24/01/2022	17:30:00	A	-
Blad1

Cell Formulas
Range	Formula
B5:B39	B5	=+B4+TIME(0,30,0)
D4:D39	D4	=IF(MEDIAN($D$2,$E$2,A4+B4)=A4+B4,C4,"-")

 

[Guzzlr]

For BSALV post

In post 7 (?) there are dates in one column and times in the next. I have to wonder what's wrong with what I suggested; i.e. why you cannot use DateDiff as long as you can string together those 2 columns as 1 date/time value? So comparing the 1st two rows:
Datediff("n",#1/3/2022 9:44:20 PM#,#1/4/2022 12:35:25 AM#)/60 = 2.85 hours. Perhaps you want to see hours and minutes, but that's another step.

Perhaps the end goal escapes me, but from what I recall reading here, the thread is about calculating the time span over midnight and not too much about what to do with the calculation.

So post 7 is the full dat set, with rows down to about 4000+ this early in the year.
Columns A and Column B exhibit a Date and Time, column G exhibits a number.
if the Time in Column B is between a range of 5:00:00PM and 04:00:00AM, then the associated number in Column G will be reported,
the link Is basically the same thing, only with out all the columns.
Sorry for any confusion
Thank you for your time

 

[Guzzlr]

To BSALV formulas in post 17, they are working!
However, I am not sure if it will work with times that have seconds and no longer in an orderly fashion every 30 minutes.
The real data set, as in post 7, have multiple time spans, and the only thing good is the descend in an orderly fashion based upon the date.


I modified the code a little to correspond with start and finish times

=IF(OR($F$3=$D5,$G$3=$D5),$E5, IF(MEDIAN($F$3,$G$3,D5)=D5,IF($F$3<$G$3,E5,"--"),IF($G$3<$F$3,E5,"-")))

 

[BSALV]

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