I’ve recently been using a tool to help me track and anticipate upcoming work so that I know what questions I need to be asking of the Contractor. This tool is the ‘Days Away Log’. It is an Excel log based on the General Contractor’s (GC) project schedule. The Days Away Log uses some simple Excel date formulas to tell the owner how many days out specific tasks are from starting or ending. This helps the owner or Construction Manager (CM) to know what documents need to be submitted and approved in the near future. I should mention that this article is written from the perspective of an Owner, Owner’s Representative, or a Construction Manager. A General Contractor can also use a Days Away Log to help him/her to anticipate what documents need to be started. I address the appropriate documents later in this article.
As I noted, the Days Away Log serves to “trigger” the owner and remind him/her of upcoming construction work. For those of you familiar with Getting Things Done (GTD) – this log acts as a “tickler” file for document administration. This log also helps to identify items that the contractor may have forgotten to submit. Before I expand, I’ve provided an image of a Days Away Log below as Figure 1:
My Days Away Log has 6 columns:
1. Task Description – Identifies the upcoming construction work as noted on the Contractor’s project schedule.
2. GC Schedule Line # - A well-organized General Contractor will have a unique line item for each task. This makes referencing easier and allows for predecessor/successor logic in Critical Path scheduling.
3. Start Date – This is the date that the specific construction task is to start. This column is hard-keyed (manually input) into the log. You should use only one of the Start/Finish columns. You will see why when I address the formulas.
4. Finish Date – This is the date that the specific construction task is to end. This column is hard-keyed (manually input) into the log. In the above log, I have only used it once for the completion of earthwork. This work was in progress and I had concerns that this task would not have been completed in time. Again, you should use only one of the Start/Finish columns.
5. Days Away – This column uses a relatively straight-forward formula to calculate the number of days until the specified line item Starts/Finishes. I show the formula below.
6. Status – This column is manually entered and helps to identify the progress of the construction work.
The “Days Away” column (Column E) uses Excel’s date subtraction function and an if/then statement. In English, it states, “If Cell C4 is blank (meaning that the Finish Column is used), then take the line’s Finish Date (Cell D4) and reduce it by today’s date (Cell E2). If the C4 cell is not blank (start date is used), then Excel uses the Start Date (Cell C4) and reduces it by today’s date ( Cell E2). The dollar signs are used for “anchoring” in Excel. This allows the user to quickly drag and copy the formula to other lines. More information on anchoring can be found here. I have used Excel’s “today” formula in Cell E2 so that I do not have to update today’s date every time I open this file. The today function is easy. In any cell simply type: “=today()”. Viola!
Content & Uses of the Days Away Log
Now that we understand the format, how can we apply the Day Away Log to our oversight of the Contractor’s work? I use this log for Submittals, Pre-Installation Meetings, Mock-Ups, and Physical Sample Review. I often use a separate excel file to work backwards from the start/finish date. I do not like to merge these 2 files because it can get confusing when it comes to the start/finish columns.
As an example, let’s review the Below Grade Waterproofing work (Line 170). This work is scheduled to start on 03/14/2016. I know that there are a number of things that need to occur before the waterproofing is installed:
- The submittal needs to be submitted, reviewed and approved.
- I know that we also need to have a pre-install meeting with the GC, 2-4 subcontractors, product reps, waterproofing consultants, and the architect.
- I also know that the pre-install meeting may also trigger revisions to the submittal and product specifications.
- Lastly, based on the time of year, we’ll probably lose some time due to the end-of-year holidays. To account for all of these tasks, I use a separate file to see how much “prep time” is necessary.
Below, is a quick schedule I drew up to outline the preparation steps leading up to the installation of the below grade waterproofing:
The first thing to note is that this is a basic schedule in Figure 3 is developed by the owner. It is not a contract document, it is not a critical path schedule and is generally not shared externally. I do not want the Contractor to accuse me of interfering with his work. It is merely intended to help the owner to understand the multiple steps needed before the waterproofing begins.
Based on my “quick-and-dirty” 10 tasks in Figure 3, I know that I need to ensure that the below-grade waterproofing paperwork starts approximately 3 months before the work is scheduled to begin. As the owner, I want my General Contractor to take the lead on these issues. In using the Days Away log and my 10 predecessor steps, I make a note for the next OAC meeting to ask about the submittal for the waterproofing and the logistics for the pre-install meeting. As a follow-up action item, I will ask the Contractor to set a deadline to submit the initial draft of the product submittal and to develop a list of people who need to attend the pre-installation meeting.
In a similar vein, this approach can also be used to determine the deadlines for resolving open design issues. If the rough-in of recessed can light fixtures starts in 4 months and we have not finalized the product specification because the original product is discontinued, the Days Away log can be used to help the Owner determine the deadline to make a decision.
Updating the Day Away Log
I update the Days Away log on a monthly basis. When I update the file, I typically update the log for the next 5-6 weeks of work. I avoid projecting any further because I know that the Contractor will provide an updated project schedule and that some tasks may shift dates. When work gets going and we have a lot of finishes and fixtures in progress (such as lights, cabinets, tile, etc.) I will update the log every other week. Lastly, I also review (but not updated) the Days Away log every other week.
The Days Away log is a relatively simple document that can greatly help one to see the future and anticipate problems before they arise. We all dislike the hurried & frenetic submittal because someone forgot about an upcoming task. The Days Away log should help to avoid these “fire drills” and help to keep all project participants operate in an anticipatory manner and not in a reactionary manner. I hope that you found this helpful and I encourage reader feedback.