How use Excel to automatically schedule dates backwards from entered date

I have an Excel file which I would like to have automatically enter dates (schedule) BACKWARDS from an entered date.  I've attached a sample file which illustrates how I would like this to work.  Can anyone help with this?


Who is Participating?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
See the attached.

barry houdiniCommented:
Hello Steve, you can use WORKDAY function. If you are using Excel 2003 then you need Analysis ToolPak installed for WORKDAY to work. If it doesn't then try enabling Analysis ToolPak like this

Tools > add-ins > tick "Analysis ToolPak"

Then formula in V3 copied back across to D3


where you have holidays listed in the range AC2:AC20

regards, barry
zorvek (Kevin Jones)ConsultantCommented:
Make a list of holidays on the sheet and name it "Holidays".

Enter this formula in cell V3:

   =WORKDAY(W3, -1, Holidays)

Copy that cell to the left.

If you are using Excel 2007, there are two functions built in: NETWORKDAYS () is the one you want.  It also will exclude holidays if you set up a holidays' table.

  If you don't have Excel 2007, the problem is much more difficult.  You basically have to CREATE a complex function that figures out the day-of-week of your begin-date (easy when you use the 2003 WEEKDAY() function!)  and then figures out how many days are left in that first week before the weekend... a simple subtraction.

   You can do the same thing with the end-date, except you need to calculate backwards to the previous weekend.

   Each of these steps will result in a MODIFIED start date.

barry houdiniConnect With a Mentor Commented:
Here' an example of the above approach

regards, barry
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.