I am working on a project where I am trying to add up times and dates together. Below is the data that I am working with.
<tbody>
</tbody>
My start date of the project is 11/1/18 @ 12:00 PM
What I am looking to do is add up the length of the project and determine when it is going to be completed. I am looking at an 8 hour work day (8am-4pm). My desired outcome should be this:
<tbody>
</tbody>
I am able to get the first two date and times but after that, the formula I am using doesn't work.
Can anyone help me with adding these time lengths together and only looking at an 8 hour day, not including weekends? Here is the formula I am using and I am not able to get it right on.
=WORKDAY(INT($C$8+SUM($E$28:E29)+VLOOKUP(SUM($E$28:E29),Sheet1!$A:$F,WEEKDAY(Dashboard!$C$8),TRUE)-1),1)+MOD(SUM($E$28:E29)+VLOOKUP(SUM($E$28:E29),Sheet1!$A:$F,WEEKDAY(Dashboard!$C$8),TRUE),1)+0.5
The vlookup is looking at a table I have created to determine the hours between days since there isn't a way to convert excels way of looking at the time. I tried to convert it from 24 to 8 so I didn't have to create a new table and account for the 16 hours between each workday or the 48 hours for the weekend. Any thoughts on this are greatly appreciated!!!! Thank you in advance!
Here is the table I created to pull the time between days. (I calculated the .33 with the previous cell + 8/24) then copied the formula all the way down. The 2,3,4,5,6 are the days of the week. 2 = Monday 6= Friday
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>
| Steps | Time Length (Days) | Time Length Converted to (H:MM) |
| Step 1 | 0.33333 | 2:40 |
| Step 2 | 2.16667 | 17:20 |
| Step 3 | 0.33333 | 2:40 |
| Step 4 | 0.29167 | 2:20 |
| Step 5 | 7.97917 | 63:50 |
| Step 6 | 0.29167 | 2:20 |
| Step 7 | 2.58333 | 20:40 |
| Step 8 | 0.29167 | 2:20 |
| Step 9 | 0.52917 | 4:14 |
| Step 10 | 0.29167 | 2:20 |
| Step 11 | 1.07292 | 8:35 |
| Step 12 | 0.45833 | 3:40 |
| Step 13 | - | 0:00 |
| Step 14 | 1.50000 | 12:00 |
<tbody>
</tbody>
My start date of the project is 11/1/18 @ 12:00 PM
What I am looking to do is add up the length of the project and determine when it is going to be completed. I am looking at an 8 hour work day (8am-4pm). My desired outcome should be this:
| Dates | Steps | Time Length (Days) | Time Length Converted to (H:MM) |
| 11/1/18 8:00 PM | Step 1 | 0.33333 | 2:40 |
| 11/5/18 4:00 PM | Step 2 | 2.16667 | 17:20 |
| 11/6/18 10:20 AM | Step 3 | 0.33333 | 2:40 |
| 11/6/18 12:40 AM | Step 4 | 0.29167 | 2:20 |
| 11/19/18 3:30 PM | Step 5 | 7.97917 | 63:50 |
| Step 6 | 0.29167 | 2:20 | |
| Step 7 | 2.58333 | 20:40 | |
| Step 8 | 0.29167 | 2:20 | |
| Step 9 | 0.52917 | 4:14 | |
| Step 10 | 0.29167 | 2:20 | |
| Step 11 | 1.07292 | 8:35 | |
| Step 12 | 0.45833 | 3:40 | |
| Step 13 | - | 0:00 | |
| Step 14 | 1.50000 | 12:00 | |
<tbody>
</tbody>
I am able to get the first two date and times but after that, the formula I am using doesn't work.
Can anyone help me with adding these time lengths together and only looking at an 8 hour day, not including weekends? Here is the formula I am using and I am not able to get it right on.
=WORKDAY(INT($C$8+SUM($E$28:E29)+VLOOKUP(SUM($E$28:E29),Sheet1!$A:$F,WEEKDAY(Dashboard!$C$8),TRUE)-1),1)+MOD(SUM($E$28:E29)+VLOOKUP(SUM($E$28:E29),Sheet1!$A:$F,WEEKDAY(Dashboard!$C$8),TRUE),1)+0.5
The vlookup is looking at a table I have created to determine the hours between days since there isn't a way to convert excels way of looking at the time. I tried to convert it from 24 to 8 so I didn't have to create a new table and account for the 16 hours between each workday or the 48 hours for the weekend. Any thoughts on this are greatly appreciated!!!! Thank you in advance!
Here is the table I created to pull the time between days. (I calculated the .33 with the previous cell + 8/24) then copied the formula all the way down. The 2,3,4,5,6 are the days of the week. 2 = Monday 6= Friday
| Days | 2 | 3 | 4 | 5 | 6 |
| 0 | - | - | - | - | - |
| 0.5 | 0.33 | 0.33 | 0.33 | 0.33 | 0.33 |
| 1.5 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| 2.5 | 1.67 | 1.67 | 1.67 | 1.67 | 1.67 |
| 3.5 | 2.33 | 2.33 | 2.33 | 2.33 | 2.33 |
| 4.5 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 |
| 5.5 | 3.67 | 3.67 | 3.67 | 3.67 | 3.67 |
| 6.5 | 4.33 | 4.33 | 4.33 | 4.33 | 4.33 |
| 7.5 | 5.00 | 5.00 | 5.00 | 5.00 | 5.00 |
| 8.5 | 5.67 | 5.67 | 5.67 | 5.67 | 5.67 |
| 9.5 | 6.33 | 6.33 | 6.33 | 6.33 | 6.33 |
| 10.5 | 7.00 | 7.00 | 7.00 | 7.00 | 7.00 |
| 11.5 | 7.67 | 7.67 | 7.67 | 7.67 | 7.67 |
| 12.5 | 8.33 | 8.33 | 8.33 | 8.33 | 8.33 |
| 13.5 | 9.00 | 9.00 | 9.00 | 9.00 | 9.00 |
| 14.5 | 9.67 | 9.67 | 9.67 | 9.67 | 9.67 |
| 15.5 | 10.33 | 10.33 | 10.33 | 10.33 | 10.33 |
| 16.5 | 11.00 | 11.00 | 11.00 | 11.00 | 11.00 |
| 17.5 | 11.67 | 11.67 | 11.67 | 11.67 | 11.67 |
| 18.5 | 12.33 | 12.33 | 12.33 | 12.33 | 12.33 |
| 19.5 | 13.00 | 13.00 | 13.00 | 13.00 | 13.00 |
| 20.5 | 13.67 | 13.67 | 13.67 | 13.67 | 13.67 |
| 21.5 | 14.33 | 14.33 | 14.33 | 14.33 | 14.33 |
| 22.5 | 15.00 | 15.00 | 15.00 | 15.00 | 15.00 |
| 23.5 | 15.67 | 15.67 | 15.67 | 15.67 | 15.67 |
| 24.5 | 16.33 | 16.33 | 16.33 | 16.33 | 16.33 |
| 25.5 | 17.00 | 17.00 | 17.00 | 17.00 | 17.00 |
| 26.5 | 17.67 | 17.67 | 17.67 | 17.67 | 17.67 |
| 27.5 | 18.33 | 18.33 | 18.33 | 18.33 | 18.33 |
| 28.5 | 19.00 | 19.00 | 19.00 | 19.00 | 19.00 |
| 29.5 | 19.67 | 19.67 | 19.67 | 19.67 | 19.67 |
| 30.5 | 20.33 | 20.33 | 20.33 | 20.33 | 20.33 |
| 31.5 | 21.00 | 21.00 | 21.00 | 21.00 | 21.00 |
| 32.5 | 21.67 | 21.67 | 21.67 | 21.67 | 21.67 |
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>
Last edited:
