Calculation completion date/time

  • Thread starter Thread starter MPI Planner
  • Start date Start date
M

MPI Planner

I am looking at added expected production hours to a start date/time and
excluding weekends, show a completion date/time

6-Mar 10:32 PM + 12.8 "hours" =?

If I add the time to the date normally I get March 7th @ 11:21 AM. This is a
Saturday however and I want to get something on Monday. I tried using the
Workday function but only got 6-Mar 0:00 which is incorrect.
 
In A1 I have 6-Mar-2009 10:32 PM
In B1 I have =12.8/24 (ie 12:28 hours)
In C1 I have =A1+B1+CHOOSE(WEEKDAY(A1+B1),1,0,0,0,0,0,2)
This should always give a weekday - did for me with limited testing
best wishes
 
MPI said:
I am looking at added expected production hours to a start date/time and
excluding weekends, show a completion date/time

6-Mar 10:32 PM + 12.8 "hours" =?

If I add the time to the date normally I get March 7th @ 11:21 AM. This is a
Saturday however and I want to get something on Monday. I tried using the
Workday function but only got 6-Mar 0:00 which is incorrect.
Click to expand...

Assuming you are counting all 24 hours except weekends, with start date/time in
A1 and hours (as a decimal value) in B1, try this:

=A1+(B1/24)+INT(((A1-INT(A1-WEEKDAY(A1,2)+1))*24+B1)/120)*2

If this doesn't fit your needs, try providing more details.
 
Hi,

Suppose your date and time are in A1 and the hours to be added are entered
as time in B1 then

=A1+B1+IF(MOD(A1+B1,7)<2,2-(MOD(A1+B1,7)<=1))
 
Bernard said:
In A1 I have 6-Mar-2009 10:32 PM
In B1 I have =12.8/24 (ie 12:28 hours)
In C1 I have =A1+B1+CHOOSE(WEEKDAY(A1+B1),1,0,0,0,0,0,2)
This should always give a weekday - did for me with limited testing
best wishes
Click to expand...


Fails if A1+B1 extends beyond Monday in the following week.
 
Shane said:
Hi,

Suppose your date and time are in A1 and the hours to be added are entered
as time in B1 then

=A1+B1+IF(MOD(A1+B1,7)<2,2-(MOD(A1+B1,7)<=1))
Click to expand...


Fails with the stated example (I get 3/8/2009 11:20 AM). Actually, only seemed
to work for results on the same week as A1 or (almost all of) the following Tuesday.
 
Back
Top