Use Formulas to Fill Weekly Schedule

[Legacy 436357]

Hi,

I have a Weekly Schedule sheet that I would to populate with formulas from the Appointments sheet.

Does anyone know how to do this?

Thank you

Excel Workbook
	A	I	K	L	M	N	O	P	Q	R
1	INDEX		8:00 AM	Lunch Start	12:00 PM		Max working hours per day			9
2				Lunch End	1:00 PM
3				Lunch Time	1:00
4	INV	JOB TIME	START DATE	DAY	START TIME	END TIME	END DATE	DAY2	START TIME2	END TIME2
5	101	6.0	8/27/2019	,"dddd")]Tuesday	8:00 AM	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),IF(]+]/24<=$M$1,]+]/24,]+]/24+($M$3)))]3:00 PM	<=$R$1,],WORKDAY(],1,Holidays))]8/27/2019	,"dddd")]Tuesday	=],"",$K$1)]	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),"")]
6	102	18.0	8/29/2019	,"dddd")]Thursday	8:00 AM	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),IF(]+]/24<=$M$1,]+]/24,]+]/24+($M$3)))]6:00 PM	<=$R$1,],WORKDAY(],1,Holidays))]8/30/2019	,"dddd")]Friday	=],"",$K$1)]8:00 AM	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),"")]6:00 PM
7	103	2.0	8/26/2019	,"dddd")]Monday	8:00 AM	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),IF(]+]/24<=$M$1,]+]/24,]+]/24+($M$3)))]10:00 AM	<=$R$1,],WORKDAY(],1,Holidays))]8/26/2019	,"dddd")]Monday	=],"",$K$1)]	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),"")]
8	104	2.0	8/27/2019	,"dddd")]Tuesday	3:30 PM	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),IF(]+]/24<=$M$1,]+]/24,]+]/24+($M$3)))]6:30 PM	<=$R$1,],WORKDAY(],1,Holidays))]8/27/2019	,"dddd")]Tuesday	=],"",$K$1)]	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),"")]
9	105	5.0	8/26/2019	,"dddd")]Monday	10:30 AM	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),IF(]+]/24<=$M$1,]+]/24,]+]/24+($M$3)))]4:30 PM	<=$R$1,],WORKDAY(],1,Holidays))]8/26/2019	,"dddd")]Monday	=],"",$K$1)]	>$R$1,IF(]+/24<=$M$1,]+/24,]+/24+($M$3)),"")]
Appointments

 

[Legacy 436357]

Sorry this is the layout with manually entered appointments:

Excel Workbook
	D	E	F	G	H
6	August 25, 2019	August 26, 2019	August 27, 2019	August 28, 2019	August 29, 2019
7
8
9
10
11		103 - Hank Williams	101 - Jack Benny	102 - Jack Benny	102 - Jack Benny
12
13
14
15		Travel Time
16		105 - Susan Maloney
17
18
19	Calculated Lunch Break Area (needs formula too)
20
21
22
23
24
25			Travel Time
26			104 - Fly By Night
27
28
29
30
31
Weekly Schedule

 

[Joe4]

Cross-posted in multiple places:
https://www.excelguru.ca/forums/show...e-Appointments
https://www.msofficeforums.com/excel...ointments.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Legacy 436357]

I won't bother you again you can remove the thread.