ElyxAI

Tax filing calendar in Excel: which client date is open

Tax AdvisorTax Filing Calendar

A tax filing calendar is one row per filing for a client you already have. You type the client, the period word, the filing type, the due date, the owner, and the alert days for that row. Days left are the due date minus today while the row is still open. A window says due when the days left are within the alert days you typed, and later when the date is still further out. The first screen is a sheet named Filings. Row 1 is the header. Row 2 is one filing you can check by hand: a client name, a period word, type vat, a due date 5 days after today, an owner, and alert days of 7. Days left are 5. The window is due, because 5 is within 7. There is no tax amount on that screen. This sheet is not an accountant's payment schedule. That list follows amounts you pay or collect. This list follows filing dates you typed. It is not a fiscal-year calendar of which months belong to a year, and it does not calculate tax on a price. It is not a legal matter deadline. The type is a word you type so you can filter. The sheet does not tell you which date the law requires. You type the date. The question is which open row is inside the warning you typed. Filed and dropped rows drop out of the window. They stay on the sheet. Alert days are a number you type, because two filing types do not need the same warning. Elyx can write the days left and the window. You still type the due date, the type, and the owner. The trial lasts 7 days, with no card.

The Problem

The calendar fails when it lives in a notebook. A line says a filing is soon. Nobody can sort by due date, and nobody can see which row is already inside the warning you wanted for that client. A sentence without a date is not a calendar. A second failure is mixing filings with payments or with legal matters. An accountant's payment schedule answers which amount is due. A fiscal-year calendar answers which months belong to a year. A legal deadline list answers which matter date is open. If you paste payment amounts, month grids, or court dates here, the morning filter no longer means which client filing is open. Keep this sheet to clients, period words, types, owners, and the dates you typed. A third failure is deleting a filing when the date passes. Set the status to filed or dropped and leave the row. The window goes blank. Deleting the row removes the only record of the date you already passed. The sheet still does not calculate a tax, and it still does not say whether a filing met a legal date.

Benefits

Each filing is one row with a due date, a type, an owner, and alert days you typed.

Days left are the due date minus today while the row is open.

Due means days left are within the alert days on that row.

Filed and dropped rows stay on the sheet and drop out of the due count.

The type is a filter. It is not a tax result.

Step-by-Step Tutorial

1

Create the list and the header

Name the sheet Filings. In row 1 type: Client, Period, Type, Due, Owner, Alert days, Status, Days left, Window, Note. Client is the name you use. Period is a word you type. Type is a word from a short list. Due is a real date. Owner is the person who holds the date. Alert days is a whole number you typed: how many days before the date you want the row on the morning list. Days left and Window are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.

One row per filing date. A client with two filing types is two rows. Do not pack both dates into one cell.

2

Lock the status words and the type words

Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open, filed, and dropped. Open means the date is still yours to meet. Filed means that filing date is done. Dropped means the date no longer applies. Only open stays in the due window. Select Type. The source is vat, corporate, and income. Add another word on the Lists sheet if you need one. People must pick a word. A synonym such as tax or sent will not match the formulas. The list is a filter. It is not a statement of what the law requires.

Alert days are not a status. They are a number. Do not type 7 in the status column.

3

Enter one filing you can check by hand

On row 2 type a client, a period word, type vat, a due date 5 days after today, an owner, and alert days of 7. Set status to open. You will predict days left as 5 and the window as due, because 5 is within 7. On row 3 use a due date 30 days out and alert days of 7. That window should be later. On row 4 use the 5-day date and set status to filed. Days left and the window should go blank. Use dates you can count on a calendar before you trust the formulas.

If you do not know how much warning you want, leave Alert days blank. The window stays blank. Do not invent a number to make the cell look finished.

4

Write days left

Days left are blank when status is filed or dropped, or when the due date is blank. Otherwise they are the due date minus today. With Due in D2 and Status in G2, use =IF(OR(G2="filed",G2="dropped",D2=""),"",D2-TODAY()). Format as a number, not a date. A negative number means the due date is already past and the row is still open. That is a signal to open the row. It is not a statement about a legal duty.

=IF(OR(G2="filed",G2="dropped",D2=""),"",D2-TODAY())

TODAY moves each morning. A row that was later can become due without anyone editing the due date.

5

Write the window

The window is blank when status is filed or dropped, or when the due date or the alert days are blank. Otherwise it is due when the due date minus today is less than or equal to the alert days, and later when the date is further out. Use =IF(OR(G2="filed",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later")). Due means the remaining days are inside the warning you typed on that row. Later means they are not. The type and the owner are not in this formula. There is no tax rate in this formula.

=IF(OR(G2="filed",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later"))

Two filing types can have different alert days. Type the number on the row. Do not point every row at one hidden 7 unless you mean that.

6

Count the rows that are due

On a sheet named Morning, the due rows are =COUNTIFS(Filings!I:I,"due"). Due rows for the owner named in A2 are =COUNTIFS(Filings!E:E,A2,Filings!I:I,"due"). Due rows of type vat are =COUNTIFS(Filings!C:C,"vat",Filings!I:I,"due"). Filed and dropped rows are out because their window is blank. A blank window is not due. The header says Window, not due. Write the rule next to the count. Sort by Due, oldest first, when you want the nearest dates at the top. The count does not require that sort.

=COUNTIFS(Filings!I:I,"due")

Do not count visible rows by hand after a filter and type over the formula.

7

Read a past due date as a signal, not as a deletion

If Days left is negative and status is still open, the due date has passed and the row was not marked filed or dropped. That is a row to open, not a row to delete. Either the date is wrong, or the status was not updated. Fix the cell that is wrong. The negative number does not say a filing was late under the law. It says the date you typed is in the past and the status is still open. A days-left number on a row marked filed should not happen, because filed blanks the cell. If you see a number on a filed row, the formula was overwritten.

Filter Window to due for the morning list. Filter Type to vat when you only want those rows. Filter Status to filed when you need history.

8

Check three rows before you share the count

Due date 5 days from today and alert days 7, status open: days left 5, window due. Due date 30 days out and alert days 7, status open: window later, because 30 is not within 7. The same 5-day date with status filed: days left blank, window blank, and the due count ignores the row. If a check fails, fix the date format or the status word before you send the count. The count is only this sheet. It is not a tax calculation, and it is not legal advice.

Write the three checks on Morning if more than one person updates filings.

Template Features

Days left while the filing is open

Due date minus today. Blank when status is filed or dropped.

=IF(OR(G2="filed",G2="dropped",D2=""),"",D2-TODAY())

Window against that row's alert

Due when days remaining are within the alert days you typed. Later otherwise. Blank when closed.

=IF(OR(G2="filed",G2="dropped",D2="",F2=""),"",IF(D2-TODAY()<=F2,"due","later"))

Due count by owner

COUNTIFS reads the owner and the window word.

=COUNTIFS(Filings!E:E,A2,Filings!I:I,"due")

Due count by type

The type is a word you typed. The count does not compute a tax.

=COUNTIFS(Filings!C:C,"vat",Filings!I:I,"due")

History stays on the sheet

Filed and dropped rows remain. They leave the due count because the window is blank.

Concrete Examples

Inside the alert you typed

You want a filing due in 5 days on this morning's list when the alert is 7 days.

Type vat. Due: 5 days after today. Alert days: 7. Status: open.

Result: Days left are 5. Window is due. The due count includes the row. The 7 is the number you typed, not a legal period.

Still later than the alert

A filing is 30 days out. Alert days on that row are 7. You do not want it on this morning's list.

Type corporate. Due: 30 days after today. Alert days: 7. Status: open.

Result: Days left are 30. Window is later, because 30 is greater than 7. Change alert days to 30 and the same date becomes due.

A filed row leaves the list

The filing was sent. You want it out of the due count and still visible.

Status changed from open to filed. Due date unchanged.

Result: Days left become blank. Window becomes blank. The due count drops the row. The due date is still on the sheet.

Pro Tips

One alert number per filing

One filing type may need a longer warning than another. Type the days on the row. If a whole type shares one number, point those rows at a labeled cell so a later edit is obvious.

Type is a label

vat, corporate, and income let you filter. Adding a word does not compute a tax and does not state a legal date. The window formula never reads the type.

Text dates do not subtract

If Due was pasted as text, D2-TODAY() will not be a day count. Re-enter the date. Then read the window again.

Distinct from payments and from legal deadlines

Amounts you pay belong on the accountant payment schedule. Matter dates belong on the legal list. Months of a fiscal year belong on a fiscal-year calendar. Do not paste a payment amount into Client and expect this count to mean the same thing.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Tax filing calendar, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions