# Calculate Work-back Schedule - Business Days and Hours

#### jlapid

##### New Member
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

Last edited by a moderator:

#### Yongle

##### Well-known Member
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.
(the date and time that the work needs to be completed to satisfy workday constraints)

= 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
DATE
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

#### jlapid

##### New Member
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

##### Well-known Member

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
(delivery time
is known)
In C3
copied down
Start Time In E2
copied down
2
2​
24/12/2019 16:00
24/12/2019 14:00​
=INDEX(List,MATCH(C2,List,1)-B2)
3
15​
24/12/2019 14:00​
=E2
20/12/2019 17:00​
4
20​
20/12/2019 17:00​
18/12/2019 15:00​
5
5​
18/12/2019 15:00​
18/12/2019 10:00​
6
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