Calculate Work-back Schedule - Business Days and Hours

jlapid · Oct 26, 2019

Hello,

I am trying to create a work-back schedule where you enter a launch date/time and the delivery dates of that project are calculated based on hours working backward from that launch date. The working hours should only be business days and hours (M-F, non-holidays, 9am-5pm).

The formulas I have seem to be working properly if the launch date is within the business day/hours but not when it is outside of that.

Any help would be appreciated.

Assets Due:
=IF(17-MOD($C$13,1)*24< D7,$C$13+D7/24,WORKDAY($C$13,1+INT((D7-(17-MOD($C$13,1)*24))/8))+"09:00"+MOD((D7-(17-MOD($C$13,1)*24)),8)/24)

eProof Delivery:
=IF(17-MOD($C$13,1)*24< D8,$C$13+D8/24,WORKDAY($C$13,1+INT((D8-(17-MOD($C$13,1)*24))/8))+"09:00"+MOD((D8-(17-MOD($C$13,1)*24)),8)/24)

Count Delivery:
=IF(17-MOD($C$13,1)*24< D9,$C$13+D9/24,WORKDAY($C$13,1+INT((D9-(17-MOD($C$13,1)*24))/8))+"09:00"+MOD((D9-(17-MOD($C$13,1)*24)),8)/24)

Major Changes request by:
=IF(17-MOD($C$13,1)*24< D10,$C$13+D10/24,WORKDAY($C$13,1+INT((D10-(17-MOD($C$13,1)*24))/8))+"09:00"+MOD((D10-(17-MOD($C$13,1)*24)),8)/24)

Minor Changes request by:
=IF(17-MOD($C$13,1)*24< D11,$C$13+D11/24,WORKDAY($C$13,1+INT((D11-(17-MOD($C$13,1)*24))/8))+"09:00"+MOD((D11-(17-MOD($C$13,1)*24)),8)/24)

Final Approval Date and Time:
=IF(17-MOD($C$13,1)*24< D12,$C$13+D12/24,WORKDAY($C$13,1+INT((D12-(17-MOD($C$13,1)*24))/8))+"09:00"+MOD((D12-(17-MOD($C$13,1)*24)),8)/24)

Here's a link to the workbook
https://www.dropbox.com/s/bj1b9j9hx1l1acj/workback schedule.xlsx?dl=0

Yongle · Oct 26, 2019

The formulas I have seem to be working properly if the launch date is within the business day/hours but not when it is outside of that.

Formulas could be based instead on calculated DEADLINE DATE and DEADLINE TIME
(the date and time that the work needs to be completed to satisfy workday constraints)

Deadline Date and Deadline Time
= Launch Date and Time (LD = working day LT = 9am -5pm)
OR
= 5pm on Launch Date (LD = working day LT = after 5pm)
OR
= 5pm on day prior to Launch Date (LD = working day LT = before 9am)
OR
= 5pm on previously available working day if any of the above calculated dates are non working dates

Use workings to derive both Deadline Date and Deadline Time from Launch Date and Launch Time as illustrated below
- note formulas in columns C, D and E

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	Launch Date	Launch Time	Lookup Date	DEADLINE DATE	DEADLINE TIME	FORMULA copied down
2	01-Dec	5:00 AM	30-Nov	29-Nov	17:00	=IF(B2<9/24,A2-1,A2)	C2
3	01-Dec	8:30 AM	30-Nov	29-Nov	17:00	=INDEX(WorkDays,MATCH(C2,WorkDays,1))	D2
4	01-Dec	12:00 PM	01-Dec	29-Nov	12:00	=IF(AND(B2>=9/24,B2<=17/24),B2,17/24)	E4
5	01-Dec	4:30 PM	01-Dec	29-Nov	16:30
6	01-Dec	7:00 PM	01-Dec	29-Nov	17:00
7	02-Dec	5:00 AM	01-Dec	29-Nov	17:00
8	02-Dec	8:30 AM	01-Dec	29-Nov	17:00
9	02-Dec	12:00 PM	02-Dec	02-Dec	12:00
10	02-Dec	4:30 PM	02-Dec	02-Dec	16:30
11	02-Dec	7:00 PM	02-Dec	02-Dec	17:00
12	15-Dec	5:00 AM	14-Dec	13-Dec	17:00
13	15-Dec	8:30 AM	14-Dec	13-Dec	17:00
14	15-Dec	12:00 PM	15-Dec	13-Dec	12:00
15	15-Dec	4:30 PM	15-Dec	13-Dec	16:30
16	15-Dec	7:00 PM	15-Dec	13-Dec	17:00
17	16-Dec	5:00 AM	15-Dec	13-Dec	17:00
18	16-Dec	8:30 AM	15-Dec	13-Dec	17:00
19	16-Dec	12:00 PM	16-Dec	16-Dec	12:00
20	16-Dec	4:30 PM	16-Dec	16-Dec	16:30
21	16-Dec	7:00 PM	16-Dec	16-Dec	17:00
22	18-Dec	5:00 AM	17-Dec	17-Dec	17:00
23	18-Dec	8:30 AM	17-Dec	17-Dec	17:00
24	18-Dec	12:00 PM	18-Dec	18-Dec	12:00
25	18-Dec	4:30 PM	18-Dec	18-Dec	16:30
26	18-Dec	7:00 PM	18-Dec	18-Dec	17:00
27	19-Dec	5:00 AM	18-Dec	18-Dec	17:00
28	19-Dec	8:30 AM	18-Dec	18-Dec	17:00
29	19-Dec	12:00 PM	19-Dec	19-Dec	12:00
30	19-Dec	4:30 PM	19-Dec	19-Dec	16:30
31	19-Dec	7:00 PM	19-Dec	19-Dec	17:00
32	20-Dec	5:00 AM	19-Dec	19-Dec	17:00
33	20-Dec	8:30 AM	19-Dec	19-Dec	17:00
34	20-Dec	12:00 PM	20-Dec	20-Dec	12:00
35	20-Dec	4:30 PM	20-Dec	20-Dec	16:30
36	20-Dec	7:00 PM	20-Dec	20-Dec	17:00
37	21-Dec	5:00 AM	20-Dec	20-Dec	17:00
38	21-Dec	8:30 AM	20-Dec	20-Dec	17:00
39	21-Dec	12:00 PM	21-Dec	20-Dec	12:00
40	21-Dec	4:30 PM	21-Dec	20-Dec	16:30
41	21-Dec	7:00 PM	21-Dec	20-Dec	17:00
42	22-Dec	5:00 AM	21-Dec	20-Dec	17:00
43	22-Dec	8:30 AM	21-Dec	20-Dec	17:00
44	22-Dec	12:00 PM	22-Dec	20-Dec	12:00
45	22-Dec	4:30 PM	22-Dec	20-Dec	16:30
46	22-Dec	7:00 PM	22-Dec	20-Dec	17:00
47	23-Dec	5:00 AM	22-Dec	20-Dec	17:00
48	23-Dec	8:30 AM	22-Dec	20-Dec	17:00
49	23-Dec	12:00 PM	23-Dec	23-Dec	12:00
50	23-Dec	4:30 PM	23-Dec	23-Dec	16:30
51	23-Dec	7:00 PM	23-Dec	23-Dec	17:00
52	24-Dec	5:00 AM	23-Dec	23-Dec	17:00
53	24-Dec	8:30 AM	23-Dec	23-Dec	17:00
54	24-Dec	12:00 PM	24-Dec	24-Dec	12:00
55	24-Dec	4:30 PM	24-Dec	24-Dec	16:30
56	24-Dec	7:00 PM	24-Dec	24-Dec	17:00
57	25-Dec	5:00 AM	24-Dec	24-Dec	17:00
58	25-Dec	8:30 AM	24-Dec	24-Dec	17:00
59	25-Dec	12:00 PM	25-Dec	24-Dec	12:00
60	25-Dec	4:30 PM	25-Dec	24-Dec	16:30
61	25-Dec	7:00 PM	25-Dec	24-Dec	17:00
62	26-Dec	5:00 AM	25-Dec	24-Dec	17:00
63	26-Dec	8:30 AM	25-Dec	24-Dec	17:00
64	26-Dec	12:00 PM	26-Dec	24-Dec	12:00
65	26-Dec	4:30 PM	26-Dec	24-Dec	16:30
66	26-Dec	7:00 PM	26-Dec	24-Dec	17:00
67	27-Dec	5:00 AM	26-Dec	24-Dec	17:00
68	27-Dec	8:30 AM	26-Dec	24-Dec	17:00
69	27-Dec	12:00 PM	27-Dec	24-Dec	12:00
70	27-Dec	4:30 PM	27-Dec	24-Dec	16:30
71	27-Dec	7:00 PM	27-Dec	24-Dec	17:00
72	28-Dec	5:00 AM	27-Dec	24-Dec	17:00
73	28-Dec	8:30 AM	27-Dec	24-Dec	17:00
74	28-Dec	12:00 PM	28-Dec	24-Dec	12:00
75	28-Dec	4:30 PM	28-Dec	24-Dec	16:30
76	28-Dec	7:00 PM	28-Dec	24-Dec	17:00
77	29-Dec	5:00 AM	28-Dec	24-Dec	17:00
78	29-Dec	8:30 AM	28-Dec	24-Dec	17:00
79	29-Dec	12:00 PM	29-Dec	24-Dec	12:00
80	29-Dec	4:30 PM	29-Dec	24-Dec	16:30
81	29-Dec	7:00 PM	29-Dec	24-Dec	17:00

Sheet: Sheet6

Column D uses Named Range WorkDays - these are the working days in December (hence 24 December deadline for many rows) - illustrated below

Excel 2016 (Windows) 32 bit

	A
1	Working Dates Earliest to Latest
2	29-Nov
3	02-Dec
4	03-Dec
5	04-Dec
6	05-Dec
7	06-Dec
8	09-Dec
9	10-Dec
10	11-Dec
11	12-Dec
12	13-Dec
13	16-Dec
14	17-Dec
15	18-Dec
16	19-Dec
17	20-Dec
18	23-Dec
19	24-Dec

Sheet: WorkDays

Comment
Some of my assumptions may be incorrect, but the general approach should work
I did not download your workbook

jlapid · Oct 26, 2019

Thanks Yongle, I like the idea of breaking out the date and time into separate columns and formulas but I am struggling how you apply the number of hours it takes to "do the work".

In my original screenshot the column header of "1-2 versions" lists out how many business hours prior to the launch time we need to calculate what you are calling the "deadline time".

Yongle · Oct 27, 2019

How about this as a much simpler approach to everything

1. Create a lookup list of valid working dates and hours starting 9 am - 5pm (see below)
(9 rows per day , approx 2,500 rows per annum - so 10 years into future are only 25,000 rows)

Excel 2016 (Windows) 32 bit

	A
1	Working Date and Times
2	13/12/2019 09:00
3	13/12/2019 10:00
4	13/12/2019 11:00
5	13/12/2019 12:00
6	13/12/2019 13:00
7	13/12/2019 14:00
8	13/12/2019 15:00
9	13/12/2019 16:00
10	13/12/2019 17:00
11	16/12/2019 09:00
12	16/12/2019 10:00
13	16/12/2019 11:00
14	16/12/2019 12:00
15	16/12/2019 13:00
16	16/12/2019 14:00
17	16/12/2019 15:00
18	16/12/2019 16:00
19	16/12/2019 17:00
20	17/12/2019 09:00
21	17/12/2019 10:00
22	17/12/2019 11:00
23	17/12/2019 12:00
24	17/12/2019 13:00
25	17/12/2019 14:00
26	17/12/2019 15:00
27	17/12/2019 16:00
28	17/12/2019 17:00
29	18/12/2019 09:00
30	18/12/2019 10:00
31	18/12/2019 11:00
32	18/12/2019 12:00
33	18/12/2019 13:00
34	18/12/2019 14:00
35	18/12/2019 15:00
36	18/12/2019 16:00
37	18/12/2019 17:00
38	19/12/2019 09:00
39	19/12/2019 10:00
40	19/12/2019 11:00
41	19/12/2019 12:00
42	19/12/2019 13:00
43	19/12/2019 14:00
44	19/12/2019 15:00
45	19/12/2019 16:00
46	19/12/2019 17:00
47	20/12/2019 09:00
48	20/12/2019 10:00
49	20/12/2019 11:00
50	20/12/2019 12:00
51	20/12/2019 13:00
52	20/12/2019 14:00
53	20/12/2019 15:00
54	20/12/2019 16:00
55	20/12/2019 17:00
56	23/12/2019 09:00
57	23/12/2019 10:00
58	23/12/2019 11:00
59	23/12/2019 12:00
60	23/12/2019 13:00
61	23/12/2019 14:00
62	23/12/2019 15:00
63	23/12/2019 16:00
64	23/12/2019 17:00
65	24/12/2019 09:00
66	24/12/2019 10:00
67	24/12/2019 11:00
68	24/12/2019 12:00
69	24/12/2019 13:00
70	24/12/2019 14:00
71	24/12/2019 15:00
72	24/12/2019 16:00
73	24/12/2019 17:00

Sheet: LookUp

2. That List can be looked up and offset based on end time to determine stat time of each task as illustrated beow
"List" is name of named range with RefersTo =LookUp!$A:$A

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	TASK	Time taken	End Time (delivery time is known)	In C3 copied down	Start Time	In E2 copied down
2	Task5	2	24/12/2019 16:00		24/12/2019 14:00	=INDEX(List,MATCH(C2,List,1)-B2)
3	Task4	15	24/12/2019 14:00	=E2	20/12/2019 17:00
4	Task3	20	20/12/2019 17:00		18/12/2019 15:00
5	Task2	5	18/12/2019 15:00		18/12/2019 10:00
6	Task1	19	18/12/2019 10:00		16/12/2019 09:00
7
8	PROOF
9	Total hours =	61
10
11	End time row	72
12	Start time row	11
13	Time taken	61
14
15

Sheet: Task

Calculate Work-back Schedule - Business Days and Hours

jlapid

New Member

Excel Facts

Yongle

Well-known Member

jlapid

New Member

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

Calculate Work-back Schedule - Business Days and Hours

jlapid

New Member

Excel Facts

Yongle

Well-known Member

jlapid

New Member

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock