Deadline Visual Aid (Heat Map)

[Shawn Miller]

Hello,

I am looking for advice. Attached is a file that has many different deadlines for states in the US regarding tax due dates. I also have a second tab with a slightly different visual but still not good.

I am trying to think of a way to visually depict our deadlines and use this as sort of a tracker. I am think we could add some variables and a user could open it and enter today's date (or populate with TODAY()) and another field where you dictate number of days out (for example, 10). Then the file would return what is due in the next 10 days. We could even have a helper column where you check off what is done so the returns only show not completed.

My struggle is thinking of a good idea to do this and reproducing it each year (see the deadlines may be listed as 3/15 so each year I would have to produce a year or add year to each of the deadlines.) Also struggling with ideas to set this up.

If anyone has creative ideas or has similar examples they could share I would appreciate it!

Thanks,
Shawn

 

[NARAYANK991]

Hi ,

My first point :

Would it be possible to have the data , in the column labelled Payment Date in the tab named Filing & Pymt Dates , as dates , and not free text the way it is now ?

You can take any year as the base year , and re-enter all of the data in the form of dates. There is no need to have multiple dates in the column. Based on the data in the column labelled Payment Period , we could have a lookup table , which could be used to generate the recurring dates based on the initial start date and the payment period.

It would facilitate all further reporting.

Narayan

 

[Shawn Miller]

Hi there,

Yes I should have stated, this is actually a file that fellow colleagues used as a tracker. I can modify and definitely need to modify the file.

What are your you thinking in the second paragraph? Have a separate cell for each unique date and just use some of the date functions to make this dynamic?

Thanks,
Shawn

 

[p45cal]

Which of the following are right and which not?:
1. 2 entries for NJ, both Tax and the same dates (rows 46 & 47 of your sheet).
2. 2 entries for WY, one Tax (with a trailing space) and one Fee, both the same dates (rows 77 & 78)
On to the your yellow highlighted entries:
3. MD showing as Semi Annual but having 4 dates in coulmn D.
4. ME with some oddly spaced dates: 4/30; 6/25; 11/2; 3/16.
5. RI with some oddly spaced dates: 4/30; 6/30; 10/30; 12/31. Sometimes 2 months apart, sometimes 4 months, not always at the end of the month, not always the 30th.

If some of these are correct it's going to be difficult to rely on column C to decide upon payment dates, which would be relatively easy; you'd just need one date in the Payment Date column as Narayan suggests (any payment date in the past, perhaps even the first payment date)

If you had such a starting payment date in column D, and today's date in cell F1 (or any other date there for testing) then the following formula in any cell in row 4 should give you the Last Payment Date:

=IF(D4="End of Month",EOMONTH($F$1,-1),EDATE(D4,DATEDIF(D4,$F$1-1,"m")-MOD(DATEDIF(D4,$F$1-1,"m"),LOOKUP(C4,{"Annual",12;"Monthly",1;"Quarterly",3;"Semi-Annual",6}))))

Then based on that you can get the Next Payment Date with:

=IF(D4="End of Month",EOMONTH($F$1,0),EDATE(F4,LOOKUP(C4,{"Annual",12;"Monthly",1;"Quarterly",3;"Semi-Annual",6})))

(In that formula, F4 is where the first formula was.)

 

[Shawn Miller]

Hello,

To answer your questions, for NJ it is 2 separate taxes and for WY it is both a tax and a fee. For our main data, there are other fields that flag what type of tax or fee it is that I can reference to. The ME and RI dates are odd but they are correct, for MD I had that flagged as yellow to review.

I think your feedback gives me 95% of the solution I need! Attached is a revised example based on your input. I need to finish filling all of these out and within the DATE() formula so formatting will be good and I need to test some more just to make sure all is good. With this logic, I can think through conditional formatting or some visual way to show the user "this is what is due in the next X days." If I only have ponder or manually review on the couple that are odd, that is no problem by me!

Any ideas on make this visually "pop out" for a user? That is definitely my weakness in Excel. If nothing else there is simple conditional formatting.

Thank you!
Shawn

 

[NARAYANK991]

Hi ,

Something like this ?

Or do you want the entire row to be highlighted ?

Narayan

 

[Shawn Miller]

This is perfect, I just tweaked it to include a condition where a user could enter non text in the next column (so the user can put an X or enter something like "confirmed on X date so they can track what has been completed and just filter to highlighted dates).

Thank you both for your help, I was stuck on this and I knew I just need a good nudge!