Keeping up with deadlines using a dynamic spreadsheet

By Kelly L. Williams, CPA, Ph.D.

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 We regret being unable to individually answer all submitted questions.


Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.


Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.