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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"MPI Planner" <(E-Mail Removed)> wrote in message
news:45A939FD-1214-4550-85D0-(E-Mail Removed)...
>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.
>
>

MPI Planner wrote:
> 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.

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MPI Planner" wrote:

> 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 wrote:
> 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.

Pity!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Glenn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bernard Liengme wrote:
>> 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.

"MPI Planner" wrote:
> > 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.
> >
> >


Shane Devenshire wrote:
> 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.