Project Completion Date & Time (No VBA)

[andrew_milonavic]

Hi Everyone,

How can I calculate the date/time a project will be completed based on a 30 hour project, 6am start, 8 hour shifts (assume breaks have been subtracted and will be working weekends)? The end date is easy but I cannot seem to get the end time right.

Example below

Any help would be great!

Thanks

Andrew

A	B	C	D	F	G
Project Duration (Hours)​	Start Date​	Start Time​	Shift Duration (Hours)​	End Date​	End Time​
30​	1/21/2020​	6:00 AM​	8​

 

[steve the fish]

These two should work.

End Date: =B2+INT(A2/D2)-(INT(A2/D2)=(A2/D2))
End Time: =MOD(A2,D2)/24+C2+(D2/24*(MOD(A2,D2)=0))

 

[andrew_milonavic]

Thanks Steve

That works perfectly!!! I really appreciate it!

Andrew

 

[steve the fish]

The only time it will look strange is if you have zero for the number of hours for project completion so you could put an IF in if that is required to test the A2 cell.

 

[andrew_milonavic]

Thanks Steve,

I added the If statement.

Follow up / Addition: If I wanted the next row down (next project) to start (time wise) when the first one ended, how could I do that while keeping to the same daily schedule? Changing the start time on the second project to be the end of the first obviously changes the hours per day etc and throws everything off.

Thoughts?

Thanks

Andrew