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.

techqa-1


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.

SPONSORED REPORT

Get your clients ready for tax season

These year-end tax planning strategies address recent tax law changes enacted to help taxpayers deal with the pandemic, such as tax credits for sick leave and family leave and new rules for retirement plan distributions, as well as techniques for putting your clients in the best possible tax position.

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.