Calculation completion date/time

[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.

 

[Bernard Liengme]

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

 

[Glenn]

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.

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.

 

[Shane Devenshire]

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))

 

[Glenn]

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

Fails if A1+B1 extends beyond Monday in the following week.

 

[Bernard Liengme]

Pity!

 

[Glenn]

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))

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.