Compare Multiple Dates - Recalculate based on Start and End Dates

[jrball]

My XL2BB code below is to attempt to recalculate attendees join and leave times that were recorded and use the Session Start Date and Times to measure and calculate the total duration of the time spent in the session per attendee per line.

The calculations are correctly working in column F where I am updated a recalculated join time based on the start date and time. For example, if join time is before 6:00, the recalculated join time in F gets set to the start time of 6:00, and if I join after 6:00 it's set to the time I joined.

Question #1 - I haven't accounted for an attendee joining after the End Date and Time, but I think I will have to, and not sure how to solve. (Maybe it's similar to the question #2 below on Leave Time.)

I am struggling with how to address the column G fields to properly recalculate the correct date and time based on the B2 Start Date and Time, and the B3 End Date and Time.

The calculated leave time column is where I am having some problems due to the leave time being prior to both the start and end times. The row 9 entry for attendee 2 joined and left before the start time, so they shouldn't get 12s of duration, but should get 0s of duration.

Question #2 - How to solve for cancelling out calculated join time and leave time to show if Join Time (C) AND Leave Time (D) is prior to Start Date and Time (B2) then the Calculated Leave Time (G) should be = B2

So the output for the Line 9 would be for the Columns F and G both equal to 2/1/2023 6:00 and then the calculated duration would = 0

The exception would be if the Calculated Leave time for other values in that column would be recalculated to the Session End date of 2/1/2023 7:30 if the attendee's leave time from column D is later than the session end date and time.

I can get one or the other to work for question #2 and can't figure out how to make both scenarios work in one formula.

Attend.xlsx
	A	B	C	D	E	F	G	H
1
2	Start Date and Time	2/1/2023 6:00
3	End Date and Time	2/1/2023 7:30
4
5
6			Join time	Leave time	Duration	Calculated Join Time	Calculated Leave Time	Calculated Duration
7		Attendee 1	2/1/2023 6:00:56	2/1/2023 6:53:32	52m 35s	2/1/2023 6:00:56	2/1/2023 6:53:32	0:52:36
8		Attendee 1	2/1/2023 7:10:45	2/1/2023 7:30:17	19m 32s	2/1/2023 7:10:45	2/1/2023 7:30:00	0:19:15
9		Attendee 2	2/1/2023 5:58:20	2/1/2023 5:58:32	12s	2/1/2023 6:00:00	2/1/2023 5:58:32	#################
10		Attendee 2	2/1/2023 6:00:08	2/1/2023 6:53:42	53m 34s	2/1/2023 6:00:08	2/1/2023 6:53:42	0:53:34
11		Attendee 2	2/1/2023 7:10:44	2/1/2023 7:30:37	19m 53s	2/1/2023 7:10:44	2/1/2023 7:30:00	0:19:16
12		Attendee 3	2/1/2023 6:00:32	2/1/2023 6:53:42	53m 10s	2/1/2023 6:00:32	2/1/2023 6:53:42	0:53:10
13		Attendee 3	2/1/2023 7:10:44	2/1/2023 7:30:08	19m 23s	2/1/2023 7:10:44	2/1/2023 7:30:00	0:19:16
14		Attendee 4	2/1/2023 5:56:26	2/1/2023 6:53:43	57m 17s	2/1/2023 6:00:00	2/1/2023 6:53:43	0:53:43
15		Attendee 4	2/1/2023 7:11:20	2/1/2023 7:28:27	17m 6s	2/1/2023 7:11:20	2/1/2023 7:28:27	0:17:07
Sheet1

Cell Formulas
Range	Formula
F7:F15	F7	=IF(C7<=$B$2,$B$2,C7)
G7:G15	G7	=IF(D7>=$B$3,$B$3,D7)
H7:H15	H7	=G7-F7

 

[Alex Blakenburg]

See if this works for you. Put it in G7 and copy down:

=IF(D7<F7,F7,IF(D7>=$B$3,$B$3,D7))

 

[jrball]

See if this works for you. Put it in G7 and copy down:

=IF(D7<F7,F7,IF(D7>=$B$3,$B$3,D7))

Thank you very much! that did the trick.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.