Excel Log Process
Here's a step-by-step guide for creating an advanced Excel log to update construction document control information. The log will include key features such as automatic calculation of days elapsed, color formatting based on status, and drop-down lists for document type, discipline, and status.
Step 1: Set Up the Log Structure
Create a new Excel worksheet and set up the basic columns for the document control log:
Column A: Document Number
Column B: Document Title
Column C: Document Type
Column D: Discipline
Column E: Status
Column F: Submission Date
Column G: Due Date
Column H: Days Elapsed
Column I: Remarks
Step 2: Create Drop-Down Lists for Document Type, Discipline, and Status
Prepare the Drop-Down Data:
In a separate sheet, list the values for each dropdown:
Document Type (e.g., Drawing, Specification, Report, etc.)
Discipline (e.g., Civil, Mechanical, Electrical, etc.)
Status (e.g., Approved, Pending, Rejected, etc.)
Create the Drop-Down Lists:
Select the cells in the Document Type column (Column C).
Go to the Data tab → Data Validation → Data Validation.
In the Allow field, select List.
In the Source field, select the range from the separate sheet where you listed the document types.
Repeat the same steps for the Discipline (Column D) and Status (Column E).
Step 3: Add a Formula to Calculate Days Elapsed
In the Days Elapsed column (Column H), use a formula to calculate the number of days since the document was submitted. In H2, enter this formula:
excel
Copy code
=IF(ISBLANK(F2),"", TODAY() - F2)
This formula will calculate the difference between today’s date and the submission date in Column F. It will display the number of days since the submission date. If the submission date is blank, it will show nothing.
Copy the formula down the entire column.
Step 4: Add Conditional Formatting for Days Elapsed
To add color formatting based on the number of days elapsed:
Select the Days Elapsed column (H).
Go to the Home tab → Conditional Formatting → New Rule.
Choose Format only cells that contain.
Set conditions for different formatting, for example:
Greater than 30 days → Format as Red.
Greater than 15 but less than 30 days → Format as Yellow.
Less than or equal to 15 days → Format as Green.
Set the desired formatting (e.g., background color), and click OK.
Step 5: Apply Conditional Formatting for Status
You can apply conditional formatting to the Status column (E) to indicate different document states visually:
Select the Status column (E).
Go to Home → Conditional Formatting → New Rule.
Select Format only cells that contain, and apply the following conditions:
Approved: Format as Green.
Pending: Format as Yellow.
Rejected: Format as Red.
Click OK to apply the formatting.
Step 6: Add a Formula to Calculate Remaining Days Until Due Date
If you want to track how many days are left until the document is due, you can add a column called Days Remaining (after Due Date) and use the following formula:
excel
Copy code
=IF(ISBLANK(G2), "", G2 - TODAY())
This will calculate the days remaining until the due date in Column G. If the due date has passed, the result will be a negative number, indicating that the document is overdue.
Step 7: Create a Summary Section (Optional)
You can create a small summary table at the top of the sheet to summarize key document control information, such as:
Total Documents (use COUNTA to count the number of document entries).
Pending Documents (use COUNTIF to count the documents with "Pending" status).
Overdue Documents (use COUNTIF with a condition for overdue dates).
Step 8: Protect and Lock the Sheet (Optional)
To prevent accidental edits to formulas or other critical fields, you can lock and protect the worksheet:
Select the cells you want to allow editing (e.g., document number, document title, dropdown lists).
Right-click and select Format Cells → Protection → Uncheck Locked.
Go to the Review tab → Protect Sheet, and set a password if necessary. This will prevent users from editing any locked cells while still allowing updates to the necessary fields.
Your construction document control log is now ready!
Dropdown Lists: Help ensure consistency and easy data entry for document types, disciplines, and status.
Days Elapsed: Automatically calculated and formatted to track submission progress.
Color Formatting: Provides a visual aid to highlight important information like overdue documents and their status.
You can further customize it based on your project needs.