How to design tasks tracker for daily, weekly monthly and quarterly together in Excel

Question

How to design tasks tracker for daily, weekly monthly and quarterly together in Excel

Amin 40

I am technical assistant in bakery with tasks from daillies to quarterlies. I want to create a task manager to keep track of what is to be done each day, weekly till the quarterly one. but the stratgey to design is lackking and how to remind myself oe each is lacking in terms of formulas. i need ideas and may samples as to how to work around it.

i am really familar with excel but this time i am having difficulty undestanding if the design is one sheet or seperation of these tasks into sheets. so in general i just need design sstrategy.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Ana Le 2,345 Independent Advisor

Hi Amin,

I would not separate the daily, weekly, monthly, and quarterly tasks into four worksheets. A single master table is easier to maintain, filter, and summarize.

Start with these columns:
Task ID | Task | Area | Frequency | Start Date | Last Completed | Next Due | Days Remaining | Status | Notes

Convert the range to an Excel Table with Ctrl+T. In the Frequency column, create a Data Validation list containing: Daily, Weekly, Monthly, Quarterly

Microsoft provides instructions for Create and format tables | Microsoft Support and Create a drop-down list | Microsoft Support

For a completion-based schedule, enter this formula in the Next Due column: =IF([@[Start Date]]="","",IF([@[Last Completed]]="",[@[Start Date]],SWITCH([@Frequency],"Daily",[@[Last Completed]]+1,"Weekly",[@[Last Completed]]+7,"Monthly",EDATE([@[Last Completed]],1),"Quarterly",EDATE([@[Last Completed]],3),"")))

When you finish a task, enter the completion date in Last Completed. The formula will then calculate the next occurrence. The EDATE function is designed for calculating dates a specified number of months before or after another date: EDATE function | Microsoft Support

For Days Remaining:
=IF([@[Next Due]]="","",[@[Next Due]]-TODAY())
For Status: =IF([@[Next Due]]="","",IF([@[Next Due]]<TODAY(),"Overdue",IF([@[Next Due]]=TODAY(),"Due today",IF([@[Next Due]]<=TODAY()+7,"Due within 7 days","Scheduled"))))

You can then apply conditional formatting so that Overdue is red, Due today is orange, and Due within 7 days is yellow. Filter the same table by Frequency or Status whenever you want to see only daily tasks, quarterly tasks, or items currently due. The TODAY() function uses the current date when Excel recalculates the workbook: TODAY function | Microsoft Support

One important design decision: the formula above calculates the next date from the date the task was completed. Therefore, completing a task late will shift its next due date. If a task must remain on a fixed schedule, such as every Monday or the first day of each quarter, create a separate row for each scheduled occurrence with a fixed Due Date. This also prevents missed occurrences from disappearing.

A second worksheet is useful only if you need:

A Completion Log containing every completed occurrence
A dashboard or PivotTable summarizing overdue and upcoming work

Excel formulas and conditional formatting provide visual reminders inside the workbook, but they do not send scheduled notifications by themselves. For email or Teams reminders, Microsoft documents a Power Automate and Office Scripts approach. Availability depends on your Microsoft 365 environment: Tutorial: Send weekly email reminders based on spreadsheet data - Office Scripts | Microsoft Learn

Amin 40 Reputation points

2026-08-31T13:30:51.7766667+00:00
Hi Ana Le, thank you for the swift help, i hav worked on it using sample data and thus far this is what i came up with.

formulas i modified:

=IF([@[Start Date]]="","",

IF([@[Last Completed]]="", WORKDAY([@[Start Date]],1),

SWITCH([@Frequency],

"Daily", WORKDAY([@[Last Completed]],1), "Weekly", WORKDAY([@[Last Completed]],7), "Monthly", WORKDAY(EDATE([@[Last Completed]],1),1), "Quarterly", WORKDAY(EDATE([@[Last Completed]],3),1)

)))

=IF([@[Next Due]]="","",IF([@[Next Due]]<TODAY(),"Overdue",IF([@[Next Due]]=TODAY(),"Due today",IF([@[Next Due]]<=TODAY()+7,"Due within 7 days","Scheduled")))

i find this suggestion you made below very important because it will indicate if the tasks for days, weekly etc have been completed within the month. i will a further insight as to how to go around thisconcept

A Completion Log containing every completed occurrence

A dashboard or PivotTable summarizing overdue and upcoming work
Ana Le 2,345 Reputation points Independent Advisor

2026-09-01T03:58:05.1+00:00
You are moving in the right direction Amin, and the Status and Days Remaining results in your screenshot appear consistent.

There is one point to correct in the Next Due formula. WORKDAY(date,7) adds seven working days, not seven calendar days. Similarly, WORKDAY(EDATE(date,1),1) moves one additional working day after the monthly date.

If Weekly means the same weekday next week, and Monthly/Quarterly means the same date adjusted forward only when it falls on a weekend, use:

=IF([@[Start Date]]="","",

IF([@[Last Completed]]="",

WORKDAY([@[Start Date]]-1,1),

SWITCH([@Frequency],

"Daily",WORKDAY([@[Last Completed]],1),

"Weekly",WORKDAY([@[Last Completed]]+6,1),

"Monthly",WORKDAY(EDATE([@[Last Completed]],1)-1,1),

"Quarterly",WORKDAY(EDATE([@[Last Completed]],3)-1,1),

"")))

For example, a monthly task completed on August 10 would next be due on September 10 if that is a working day, rather than September 11.

This formula assumes Monday through Friday are working days. If the bakery operates on weekends or uses a different workweek, either retain calendar-date calculations or use WORKDAY.INTL, which allows custom weekend days. Microsoft documents the difference here:

WORKDAY function | Microsoft Support

WORKDAY.INTL function | Microsoft Support

For the completion history, I recommend adding a second worksheet named Task Log. It should contain one row for every scheduled occurrence, including occurrences that have not yet been completed.

Suggested columns are:

Task ID | Task | Frequency | Due Date | Completed Date | Completed By | Completion Status | Completed Flag | Notes

The Completion Status formula can be: =IF([@[Completed Date]]="","Pending",IF([@[Completed Date]]<=[@[Due Date]],"Completed on time","Completed late"))

For Completed Flag, use: =--([@[Completed Date]]<>"")

This returns 1 when the occurrence is completed and 0 when it is still pending.

It is important to add the expected occurrences to this log in advance. If the log contains only completed tasks, it cannot show which scheduled tasks were missed. For example:

Task ID Task Frequency Due Date Completed Date Completion Status

B001 Clean mixer Daily September 1 September 1 Completed on time

B001 Clean mixer Daily September 2 Pending

You can then calculate Last Completed automatically in the main Tasks table. If the tables are named tblTasks and tblLog, use this formula in the Last Completed column of tblTasks:

=IF(COUNTIFS(tblLog[Task ID],[@[Task ID]],tblLog[Completed Date],">0")=0,"",MAXIFS(tblLog[Completed Date],tblLog[Task ID],[@[Task ID]])) MAXIFS returns the latest completion date matching that Task ID: MAXIFS function.

Finally, create a PivotTable from tblLog and arrange the fields as follows:

Rows: Frequency, followed by Task

Columns: Due Date, grouped by Months and Years

Values:

Count of Task ID, renamed Scheduled

Sum of Completed Flag, renamed Completed

Average of Completed Flag, formatted as a percentage and renamed Completion Rate

Filters or slicers: Completion Status and Area

This will show how many daily, weekly, monthly, and quarterly occurrences were scheduled and completed during each month. Microsoft provides the relevant steps here:

Create a PivotTable

Group dates in a PivotTable

(Note: If any Microsoft Support link is temporarily unavailable, please try opening it again later.)