Rounding date formula results

[martin]

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

 

[T. Valko]

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)

 

[Ron Rosenfeld]

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

 

[Trekman]

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