Keeping up with deadlines using a dynamic spreadsheet
Q. Our company develops customized project plans for clients that must go through several reviews. I am responsible for making sure each review gets done on time. Is there a way I can easily track this in Excel? Also, can the spreadsheet account for leap years?
A. Excel can keep track of deadline information in several ways. Based on the scenario you describe, Excel can calculate the number of days until a deadline (or the days past a deadline) based on the submit date and the number of months the reviewer has until the review is due. Excel also incorporates the correct number of days in any year, including a leap year.
The screenshot below shows the “Date Submitted” in column E and the “Estimated Time to Review (months)” in column G. “Time Remaining for Review Completion (Days)” is calculated with a couple of steps.
Step one, the date the review is due is calculated by using the function EDATE. This function requires the Start_date, which is the date submitted in this example (E4), and the Months, which is the number of months after, or before, the start date (G4). For this example, the first portion of the formula looks like this: EDATE(E4,G4).
Step two, we subtract today’s date from the calculation we created in the first step. Because “today’s date” changes every day, it is best to use the function TODAY instead of a static date. There are no parameters to specify when selecting the function TODAY.
Combining steps one and two produces the formula =EDATE(E4,G4)-TODAY(), with the only items to specify being the start date and the number of months after the start date.
Click here to download an Excel workbook. Please note that the workbook will recalculate the above information based on the date it is opened. The date in cell A1 and the number of days remaining in column I will not be the same as in the screenshot below. You can also view a video demonstration, below, that shows the steps described in this item.
Please note that if the “Estimated Time to Review” is recorded in days or weeks, then you don’t have to use the EDATE function. Instead, because you just add the date submitted to the number of days or weeks to produce the Time Remaining: For days: =E4+G4; or for weeks: =E4+(G4*7). The EDATE function is needed for months because the number of days in each month varies.
— By Kelly L. Williams, CPA, Ph.D.
About the authors
Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University. Byron Patrick, CPA/CITP, CGMA, is senior applications consultant at botkeeper.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
