Rounding date formula results
 

I am putting together a project plan in Excel. The task dates are driven by
the selected due date. I would like to enter the due date and have the
remaining dates populate based on pre-determined values. However, is it
possible to prevent these values from falling on a weekend (e.g. round to the
nearest workday?)

Rounding date formula results
 

One way...

Enter the first date in your first cell. Assume this is cell A1.

Enter this formula in cell A2 and copy down as needed:

=A1+CHOOSE(WEEKDAY(A1),1,1,1,1,1,3,2)

--
Biff
Microsoft Excel MVP


"martin" wrote in message
...
I am putting together a project plan in Excel. The task dates are driven
by
the selected due date. I would like to enter the due date and have the
remaining dates populate based on pre-determined values. However, is it
possible to prevent these values from falling on a weekend (e.g. round to
the
nearest workday?)

Rounding date formula results
 

On Mon, 12 Jan 2009 18:55:01 -0800, martin
wrote:

I am putting together a project plan in Excel. The task dates are driven by
the selected due date. I would like to enter the due date and have the
remaining dates populate based on pre-determined values. However, is it
possible to prevent these values from falling on a weekend (e.g. round to the
nearest workday?)

Take a look at the WORKDAY function. (If you try it and get a #NAME! error,
check HELP for the function to correct it).

A1: Seed date
A2: =WORKDAY(A1, pre-determined_value_in_days) will yield the next working
day. There is also an optional [holidays] argument you may want to use.
--ron

Rounding date formula results
 

On Jan 13, 4:07*am, Ron Rosenfeld wrote:
On Mon, 12 Jan 2009 18:55:01 -0800, martin
wrote:

I am putting together a project plan in Excel. *The task dates are driven by
the selected due date. *I would like to enter the due date and have the
remaining dates populate based on pre-determined values. *However, is it
possible to prevent these values from falling on a weekend (e.g. round to the
nearest workday?)

Take a look at the WORKDAY function. *(If you try it and get a #NAME! error,
check HELP for the function to correct it).

A1: * * Seed date
A2: * * =WORKDAY(A1, pre-determined_value_in_days) *will yield the next working
day. *There is also an optional [holidays] argument you may want to use..
--ron

Not specifically to your question, but if a "week ending" is required,
you can use the formula below. You can always ad or subtract a few
days too to get another day of the week.

=NOW()-MOD(NOW(),7)+(IF(MOD(NOW(),7)=0,0,7))