Rounding date formula results

  • Thread starter Thread starter martin
  • Start date Start date
M

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?)
 
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)
 
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?)
Click to expand...

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
 
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?)
Click to expand...

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
Click to expand...

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