Calculate Work-back Schedule - Business Days and Hours

jlapid

New Member
Joined
Oct 26, 2019
Messages
2
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
Joined
Mar 11, 2015
Messages
4,714
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 26, 2019
Messages
2
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
Joined
Mar 11, 2015
Messages
4,714
Office Version
365
Platform
Windows
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
TASKTime takenEnd 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
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top