Overview
If your project has been delayed or pushed forward and you’ve already created project visuals using the Office Timeline add-in, it can be time-consuming to manually change each date on the timeline to reflect the new schedule. Here’s a shortcut you may want to use, particularly if you have a lot of items on the schedule that need their dates updated. Using the add-in’s integration with Excel, you can use formulas to automate the process and update your project visuals faster. The following guide will show you how.
Steps
-
Export your timeline data to Excel. To do so, from your timeline, go to your Edit Data Wizard, right-click on any of the tasks to display the menu options, and select Copy data. Then, open Excel and paste it into a blank sheet.
-
You will notice that some rows in column B will be empty. This is because Milestones only have end dates, unlike Tasks (which have both start and end dates). Position the cursor in column G, corresponding to the first non-empty B cell (in our case, G10):
-
Now you will use the EDATE function, which allows you to add or subtract the desired number of months to or from a specific date. You can just type in the formula in the G10 cell. The EDATE function uses two parameters: one for the cell (or date) we are referring to and another for the value we’re adding or subtracting (the number of months). To add 6 months we will use “=EDATE(B10,6)”. If you would like to subtract 3 months, just use “=EDATE(B10,-3)” instead:
-
Once you hit Enter, Excel will automatically calculate the new date. In our case, EDATE(B10,6) will add 6 months to the date in cell B10, but it will display it as a serial number. Don’t worry, as you can easily change it to the date format: simply right-click on the result, select Format Cells and choose Date.
-
To calculate the adjusted start dates for the rest of the tasks, simply select the fill handle (bottom-right corner) of the cell where you used EDATE and drag it down. As you can see in our example, Excel has replicated the formula and added 6 months to all the task start dates.
-
Repeat the steps above to adjust the end dates (or milestone dates) as well.
-
Since these are still formulas, you will also need to transform them into values using Copy & Paste Special. Select the two new columns, right-click, and select Copy:
-
Then right click again on Paste Special just below Copy and select Values. Hit OK next:
-
Now you will have actual values instead of formulas. Replace the dates from column B & C with the new values from G and H. Your Excel file should have 6 months added to all the original dates now:
-
The final step is to paste or import your data back to Office Timeline.
Note that, when pasting back from Excel, you may lose some of your original formatting. If you use the add-in’s import function, the resulting timeline will remain linked to the Excel worksheet, allowing you to automatically update your slide if your Excel data changes again. To see how to use Office Timeline’s Refresh Data feature, please watch the video below:
Notes:
You are not limited to changing months only! You can have full control over your dates:
- If you want to add or subtract days from a date, instead of step 3, simply type in “=initial date’s cell address +/- number of days”. For instance, in our example, if we enter “=B10-3” in an empty cell, Excel will subtract 3 days from the Submittal Approval start date and display 7/22/2017.
-
You can also adjust a date by a specific number of years, months and days, using the following formula:
=DATE(YEAR(cell address)+/-years, MONTH (cell address)+/-months, DAY(cell address)+/-days)In our example, if we type “=DATE(YEAR(B10)+1,MONTH(B10)-1,DAY(B10)-3)” in an empty cell, Excel will use “7/25/2017” as reference and display 6/22/2018 as a result, because it added 1 year and subtracted 1 month and 3 days:
