Adding time and dates together

J

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35
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.

[TABLE="width: 192"]
<tbody>[TR]
[TD]Steps[/TD]
[TD]Time Length (Days)[/TD]
[TD]Time Length Converted to (H:MM)[/TD]
[/TR]
[TR]
[TD]Step 1[/TD]
[TD]0.33333[/TD]
[TD="align: right"]2:40[/TD]
[/TR]
[TR]
[TD]Step 2[/TD]
[TD]2.16667[/TD]
[TD="align: right"]17:20[/TD]
[/TR]
[TR]
[TD]Step 3[/TD]
[TD]0.33333[/TD]
[TD="align: right"]2:40[/TD]
[/TR]
[TR]
[TD]Step 4[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD]Step 5[/TD]
[TD]7.97917[/TD]
[TD="align: right"]63:50[/TD]
[/TR]
[TR]
[TD]Step 6[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD]Step 7[/TD]
[TD]2.58333[/TD]
[TD="align: right"]20:40[/TD]
[/TR]
[TR]
[TD]Step 8[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD]Step 9[/TD]
[TD]0.52917[/TD]
[TD="align: right"]4:14[/TD]
[/TR]
[TR]
[TD]Step 10[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD]Step 11[/TD]
[TD]1.07292[/TD]
[TD="align: right"]8:35[/TD]
[/TR]
[TR]
[TD]Step 12[/TD]
[TD]0.45833[/TD]
[TD="align: right"]3:40[/TD]
[/TR]
[TR]
[TD]Step 13[/TD]
[TD]-[/TD]
[TD="align: right"]0:00[/TD]
[/TR]
[TR]
[TD]Step 14[/TD]
[TD]1.50000[/TD]
[TD="align: right"]12:00[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 314"]
<tbody>[TR]
[TD]Dates[/TD]
[TD]Steps[/TD]
[TD]Time Length (Days)[/TD]
[TD]Time Length Converted to (H:MM)[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/18 8:00 PM[/TD]
[TD]Step 1[/TD]
[TD]0.33333[/TD]
[TD="align: right"]2:40[/TD]
[/TR]
[TR]
[TD="align: right"]11/5/18 4:00 PM[/TD]
[TD]Step 2[/TD]
[TD]2.16667[/TD]
[TD="align: right"]17:20[/TD]
[/TR]
[TR]
[TD="align: right"]11/6/18 10:20 AM[/TD]
[TD]Step 3[/TD]
[TD]0.33333[/TD]
[TD="align: right"]2:40[/TD]
[/TR]
[TR]
[TD="align: right"]11/6/18 12:40 AM[/TD]
[TD]Step 4[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD="align: right"]11/19/18 3:30 PM[/TD]
[TD]Step 5[/TD]
[TD]7.97917[/TD]
[TD="align: right"]63:50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 6[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 7[/TD]
[TD]2.58333[/TD]
[TD="align: right"]20:40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 8[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 9[/TD]
[TD]0.52917[/TD]
[TD="align: right"]4:14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 10[/TD]
[TD]0.29167[/TD]
[TD="align: right"]2:20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 11[/TD]
[TD]1.07292[/TD]
[TD="align: right"]8:35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 12[/TD]
[TD]0.45833[/TD]
[TD="align: right"]3:40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 13[/TD]
[TD]-[/TD]
[TD="align: right"]0:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Step 14[/TD]
[TD]1.50000[/TD]
[TD="align: right"]12:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Days[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="class: xl66"] - [/TD]
[TD="class: xl66"] - [/TD]
[TD="class: xl66"] - [/TD]
[TD="class: xl66"] - [/TD]
[TD="class: xl66"] - [/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[TD="class: xl66"] 0.33 [/TD]
[TD="class: xl66"] 0.33 [/TD]
[TD="class: xl66"] 0.33 [/TD]
[TD="class: xl66"] 0.33 [/TD]
[TD="class: xl66"] 0.33 [/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="class: xl66"] 1.00 [/TD]
[TD="class: xl66"] 1.00 [/TD]
[TD="class: xl66"] 1.00 [/TD]
[TD="class: xl66"] 1.00 [/TD]
[TD="class: xl66"] 1.00 [/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[TD="class: xl66"] 1.67 [/TD]
[TD="class: xl66"] 1.67 [/TD]
[TD="class: xl66"] 1.67 [/TD]
[TD="class: xl66"] 1.67 [/TD]
[TD="class: xl66"] 1.67 [/TD]
[/TR]
[TR]
[TD="align: right"]3.5[/TD]
[TD="class: xl66"] 2.33 [/TD]
[TD="class: xl66"] 2.33 [/TD]
[TD="class: xl66"] 2.33 [/TD]
[TD="class: xl66"] 2.33 [/TD]
[TD="class: xl66"] 2.33 [/TD]
[/TR]
[TR]
[TD="align: right"]4.5[/TD]
[TD="class: xl66"] 3.00 [/TD]
[TD="class: xl66"] 3.00 [/TD]
[TD="class: xl66"] 3.00 [/TD]
[TD="class: xl66"] 3.00 [/TD]
[TD="class: xl66"] 3.00 [/TD]
[/TR]
[TR]
[TD="align: right"]5.5[/TD]
[TD="class: xl66"] 3.67 [/TD]
[TD="class: xl66"] 3.67 [/TD]
[TD="class: xl66"] 3.67 [/TD]
[TD="class: xl66"] 3.67 [/TD]
[TD="class: xl66"] 3.67 [/TD]
[/TR]
[TR]
[TD="align: right"]6.5[/TD]
[TD="class: xl66"] 4.33 [/TD]
[TD="class: xl66"] 4.33 [/TD]
[TD="class: xl66"] 4.33 [/TD]
[TD="class: xl66"] 4.33 [/TD]
[TD="class: xl66"] 4.33 [/TD]
[/TR]
[TR]
[TD="align: right"]7.5[/TD]
[TD="class: xl66"] 5.00 [/TD]
[TD="class: xl66"] 5.00 [/TD]
[TD="class: xl66"] 5.00 [/TD]
[TD="class: xl66"] 5.00 [/TD]
[TD="class: xl66"] 5.00 [/TD]
[/TR]
[TR]
[TD="align: right"]8.5[/TD]
[TD="class: xl66"] 5.67 [/TD]
[TD="class: xl66"] 5.67 [/TD]
[TD="class: xl66"] 5.67 [/TD]
[TD="class: xl66"] 5.67 [/TD]
[TD="class: xl66"] 5.67 [/TD]
[/TR]
[TR]
[TD="align: right"]9.5[/TD]
[TD="class: xl66"] 6.33 [/TD]
[TD="class: xl66"] 6.33 [/TD]
[TD="class: xl66"] 6.33 [/TD]
[TD="class: xl66"] 6.33 [/TD]
[TD="class: xl66"] 6.33 [/TD]
[/TR]
[TR]
[TD="align: right"]10.5[/TD]
[TD="class: xl66"] 7.00 [/TD]
[TD="class: xl66"] 7.00 [/TD]
[TD="class: xl66"] 7.00 [/TD]
[TD="class: xl66"] 7.00 [/TD]
[TD="class: xl66"] 7.00 [/TD]
[/TR]
[TR]
[TD="align: right"]11.5[/TD]
[TD="class: xl66"] 7.67 [/TD]
[TD="class: xl66"] 7.67 [/TD]
[TD="class: xl66"] 7.67 [/TD]
[TD="class: xl66"] 7.67 [/TD]
[TD="class: xl66"] 7.67 [/TD]
[/TR]
[TR]
[TD="align: right"]12.5[/TD]
[TD="class: xl66"] 8.33 [/TD]
[TD="class: xl66"] 8.33 [/TD]
[TD="class: xl66"] 8.33 [/TD]
[TD="class: xl66"] 8.33 [/TD]
[TD="class: xl66"] 8.33 [/TD]
[/TR]
[TR]
[TD="align: right"]13.5[/TD]
[TD="class: xl66"] 9.00 [/TD]
[TD="class: xl66"] 9.00 [/TD]
[TD="class: xl66"] 9.00 [/TD]
[TD="class: xl66"] 9.00 [/TD]
[TD="class: xl66"] 9.00 [/TD]
[/TR]
[TR]
[TD="align: right"]14.5[/TD]
[TD="class: xl66"] 9.67 [/TD]
[TD="class: xl66"] 9.67 [/TD]
[TD="class: xl66"] 9.67 [/TD]
[TD="class: xl66"] 9.67 [/TD]
[TD="class: xl66"] 9.67 [/TD]
[/TR]
[TR]
[TD="align: right"]15.5[/TD]
[TD="class: xl66"] 10.33 [/TD]
[TD="class: xl66"] 10.33 [/TD]
[TD="class: xl66"] 10.33 [/TD]
[TD="class: xl66"] 10.33 [/TD]
[TD="class: xl66"] 10.33 [/TD]
[/TR]
[TR]
[TD="align: right"]16.5[/TD]
[TD="class: xl66"] 11.00 [/TD]
[TD="class: xl66"] 11.00 [/TD]
[TD="class: xl66"] 11.00 [/TD]
[TD="class: xl66"] 11.00 [/TD]
[TD="class: xl66"] 11.00 [/TD]
[/TR]
[TR]
[TD="align: right"]17.5[/TD]
[TD="class: xl66"] 11.67 [/TD]
[TD="class: xl66"] 11.67 [/TD]
[TD="class: xl66"] 11.67 [/TD]
[TD="class: xl66"] 11.67 [/TD]
[TD="class: xl66"] 11.67 [/TD]
[/TR]
[TR]
[TD="align: right"]18.5[/TD]
[TD="class: xl66"] 12.33 [/TD]
[TD="class: xl66"] 12.33 [/TD]
[TD="class: xl66"] 12.33 [/TD]
[TD="class: xl66"] 12.33 [/TD]
[TD="class: xl66"] 12.33 [/TD]
[/TR]
[TR]
[TD="align: right"]19.5[/TD]
[TD="class: xl66"] 13.00 [/TD]
[TD="class: xl66"] 13.00 [/TD]
[TD="class: xl66"] 13.00 [/TD]
[TD="class: xl66"] 13.00 [/TD]
[TD="class: xl66"] 13.00 [/TD]
[/TR]
[TR]
[TD="align: right"]20.5[/TD]
[TD="class: xl66"] 13.67 [/TD]
[TD="class: xl66"] 13.67 [/TD]
[TD="class: xl66"] 13.67 [/TD]
[TD="class: xl66"] 13.67 [/TD]
[TD="class: xl66"] 13.67 [/TD]
[/TR]
[TR]
[TD="align: right"]21.5[/TD]
[TD="class: xl66"] 14.33 [/TD]
[TD="class: xl66"] 14.33 [/TD]
[TD="class: xl66"] 14.33 [/TD]
[TD="class: xl66"] 14.33 [/TD]
[TD="class: xl66"] 14.33 [/TD]
[/TR]
[TR]
[TD="align: right"]22.5[/TD]
[TD="class: xl66"] 15.00 [/TD]
[TD="class: xl66"] 15.00 [/TD]
[TD="class: xl66"] 15.00 [/TD]
[TD="class: xl66"] 15.00 [/TD]
[TD="class: xl66"] 15.00 [/TD]
[/TR]
[TR]
[TD="align: right"]23.5[/TD]
[TD="class: xl66"] 15.67 [/TD]
[TD="class: xl66"] 15.67 [/TD]
[TD="class: xl66"] 15.67 [/TD]
[TD="class: xl66"] 15.67 [/TD]
[TD="class: xl66"] 15.67 [/TD]
[/TR]
[TR]
[TD="align: right"]24.5[/TD]
[TD="class: xl66"] 16.33 [/TD]
[TD="class: xl66"] 16.33 [/TD]
[TD="class: xl66"] 16.33 [/TD]
[TD="class: xl66"] 16.33 [/TD]
[TD="class: xl66"] 16.33 [/TD]
[/TR]
[TR]
[TD="align: right"]25.5[/TD]
[TD="class: xl66"] 17.00 [/TD]
[TD="class: xl66"] 17.00 [/TD]
[TD="class: xl66"] 17.00 [/TD]
[TD="class: xl66"] 17.00 [/TD]
[TD="class: xl66"] 17.00 [/TD]
[/TR]
[TR]
[TD="align: right"]26.5[/TD]
[TD="class: xl66"] 17.67 [/TD]
[TD="class: xl66"] 17.67 [/TD]
[TD="class: xl66"] 17.67 [/TD]
[TD="class: xl66"] 17.67 [/TD]
[TD="class: xl66"] 17.67 [/TD]
[/TR]
[TR]
[TD="align: right"]27.5[/TD]
[TD="class: xl66"] 18.33 [/TD]
[TD="class: xl66"] 18.33 [/TD]
[TD="class: xl66"] 18.33 [/TD]
[TD="class: xl66"] 18.33 [/TD]
[TD="class: xl66"] 18.33 [/TD]
[/TR]
[TR]
[TD="align: right"]28.5[/TD]
[TD="class: xl66"] 19.00 [/TD]
[TD="class: xl66"] 19.00 [/TD]
[TD="class: xl66"] 19.00 [/TD]
[TD="class: xl66"] 19.00 [/TD]
[TD="class: xl66"] 19.00 [/TD]
[/TR]
[TR]
[TD="align: right"]29.5[/TD]
[TD="class: xl66"] 19.67 [/TD]
[TD="class: xl66"] 19.67 [/TD]
[TD="class: xl66"] 19.67 [/TD]
[TD="class: xl66"] 19.67 [/TD]
[TD="class: xl66"] 19.67 [/TD]
[/TR]
[TR]
[TD="align: right"]30.5[/TD]
[TD="class: xl66"] 20.33 [/TD]
[TD="class: xl66"] 20.33 [/TD]
[TD="class: xl66"] 20.33 [/TD]
[TD="class: xl66"] 20.33 [/TD]
[TD="class: xl66"] 20.33 [/TD]
[/TR]
[TR]
[TD="align: right"]31.5[/TD]
[TD="class: xl66"] 21.00 [/TD]
[TD="class: xl66"] 21.00 [/TD]
[TD="class: xl66"] 21.00 [/TD]
[TD="class: xl66"] 21.00 [/TD]
[TD="class: xl66"] 21.00 [/TD]
[/TR]
[TR]
[TD="align: right"]32.5[/TD]
[TD="class: xl66"] 21.67 [/TD]
[TD="class: xl66"] 21.67 [/TD]
[TD="class: xl66"] 21.67 [/TD]
[TD="class: xl66"] 21.67 [/TD]
[TD="class: xl66"] 21.67 [/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Try this. I put the Start Date-Time in A1

[table="width: 0, class: grid, align: left"][tr][td]
[table="width: 0, class: grid, align: left"]
[tr][th][/th][th]A[/th][th]B[/th][th]C[/th][/tr]
[tr][th]1[/th][td]
Thursday, Nov 01, 2018 12:00 PM​
[/td][td]Steps[/td][td]Time Length (Days)[/td][/tr]
[tr][th]2[/th][td]
Thursday, Nov 01, 2018 2:40 PM​
[/td][td]Step 1[/td][td]
0.33333​
[/td][/tr]
[tr][th]3[/th][td]
Monday, Nov 05, 2018 4:00 PM​
[/td][td]Step 2[/td][td]
2.16667​
[/td][/tr]
[tr][th]4[/th][td]
Tuesday, Nov 06, 2018 10:40 AM​
[/td][td]Step 3[/td][td]
0.33333​
[/td][/tr]
[tr][th]5[/th][td]
Tuesday, Nov 06, 2018 1:00 PM​
[/td][td]Step 4[/td][td]
0.29167​
[/td][/tr]
[tr][th]6[/th][td]
Friday, Nov 16, 2018 12:50 PM​
[/td][td]Step 5[/td][td]
7.97917​
[/td][/tr]
[tr][th]7[/th][td]
Friday, Nov 16, 2018 3:10 PM​
[/td][td]Step 6[/td][td]
0.29167​
[/td][/tr]
[tr][th]8[/th][td]
Wednesday, Nov 21, 2018 11:50 AM​
[/td][td]Step 7[/td][td]
2.58333​
[/td][/tr]
[tr][th]9[/th][td]
Wednesday, Nov 21, 2018 2:10 PM​
[/td][td]Step 8[/td][td]
0.29167​
[/td][/tr]
[tr][th]10[/th][td]
Thursday, Nov 22, 2018 10:24 AM​
[/td][td]Step 9[/td][td]
0.52917​
[/td][/tr]
[tr][th]11[/th][td]
Thursday, Nov 22, 2018 12:44 PM​
[/td][td]Step 10[/td][td]
0.29167​
[/td][/tr]
[tr][th]12[/th][td]
Friday, Nov 23, 2018 1:19 PM​
[/td][td]Step 11[/td][td]
1.07292​
[/td][/tr]
[tr][th]13[/th][td]
Monday, Nov 26, 2018 8:59 AM​
[/td][td]Step 12[/td][td]
0.45833​
[/td][/tr]
[tr][th]14[/th][td]
Monday, Nov 26, 2018 8:59 AM​
[/td][td]Step 13[/td][td]
0​
[/td][/tr]
[tr][th]15[/th][td]
Tuesday, Nov 27, 2018 12:59 PM​
[/td][td]Step 14[/td][td]
1.5​
[/td][/tr]
[/table]
[/td][/tr][/table]

[table="width: 0, class: grid, align: left"][tr][td]Worksheet Formulas[/td][/tr][tr][td][table="width: 0, class: grid, align: left"]
[tr][th]Cell[/th][th]Formula[/th][/tr][tr][th]A2[/th][td]=WORKDAY(A1,INT(C2)+(MOD(WORKDAY(A1,INT(C2))+MOD(A1,1)+(MOD(C2,1)*8/24),1)>16/24))
+MOD(A1,1)+(MOD(C2,1)*8/24-IF(MOD(A1,1)+MOD(C2,1)*8/24>16/24,8/24,0))[/td][/tr]
[/table]
[/td][/tr][/table]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Baseball system help W or L
2
Replies
16
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top