Go Back

Get project start date

Read time: 20 minutes

In this tutorial, you will learn how to get project start date when we have start day and project duration in workdays. This is possible by using Excel WORKDAY function. This function returns a date in a specific range (before or after selected date). In our case, it will return a start date based on end date and project duration in days.

Get project start date

Formula

=WORKDAY(end_date, - duration_days, [holidays])

Explanation

The WORKDAY function returns a date in a specific range, before or after a selected date. The function can take in count specified holidays, but it is not mandatory.
The parameters of the function are: end_date – a finishing date of a project for which we want to get a start date; duration_days – a duration of a project for which we want to get a start date – in our case it will be negative, because we have to subtract it from end date; holidays is a non-obligatory parameter which enables the function to take in counts holiday dates and skip them. To enable this, we need to create a table with all holiday’s dates. Also, we need to define a name for range containing holidays’ dates.

Example 1

In the following example, we will see how to get project start date, if we have ending date and duration of a project in workdays. We will also take in count holidays while calculating the start date. Let’s see how the formula looks like:

=WORKDAY(C3, -D3, public_holidays)

 

Figure 1. Get project start date

 

As you can see in the picture, in column B (“Project”), we have project names. In column C (“End date”), we have end dates of projects, while in column D (“Workdays”) we have a length of every project in working days. In the column E (“Start date”) we want to get a start date of every project.
First of all, we created a new table which has the list of holidays and their dates and named the range G3:G6 “public_holidays”.

In the example, we want to get a project start date based on cells C3 and D3 and put it in the cell E3. Therefore, our end date will be 16-Feb-18 and project duration 15 working days. As 15-Feb-18 is the Holiday, the formula will skip this date and weekends and return 25-Jan-18 in the cell E3 as the start date of the Project 1.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to write a formula to track milestones of projects the team is working on. I want to exclude holidays and weekends. I want to be able to just enter in start and end date of project and let excel do the rest. Milestone 1 (start date) to milestone 2 will equal 4% of the project. milestone 2 to milestone 3 will equal 6% of the project. 3 to 4 will equal 15% of the project. 4 to 5 will equal 51% of the project. 5 to 6 will equal 4% of the project. 6 to 7 will equal 6% of the project. and milestone 7 to milestone 8 (end date) will equal 14% of the project.
Solved by V. Y. in 13 mins
On the tables that has a completion date, I would like to add a start date row above it and get the start date from Ticket Input sheet
Solved by D. U. in 23 mins
I have start date and end dates in 2 columns. I have other 4 columns as Quarter 1/2/3/4. Based on the duration of the project and based on start date and End date I want to populate "1" in the respective quarter columns. Example: Start Date 4/1/2019, End Date: 12/31/2019. Q1 should display "0" and Q2, Q3, Q4 should display "1"
Solved by D. B. in 19 mins

Leave a Comment

avatar