Automatic Due Date

Brandee Pak

This might be a stretch but going to put it out there.

This is my situation. Each manager has a sheet with their individual employees and what cost centers they are projected to work in for the upcoming quarter. They were sent a notification to update the sheet triggered by the date of 2 weeks prior to the due date of the information being needed by finance.

However, since each manager has an individual sheet, (I had to meet them where they were at and compromise), I am trying to avoid having to add in separate notifications for each quarter and would like it to be triggered by a due date column that automatically changes to the upcoming due dates of 1/9, 3/9, 6/9 and 9/9.

Is there a formula that could be used to default the quarterly due dates?

If yes, I feel like I can set up one notification for each that the due date is upcoming and a reminder notification if the data isn't enter 1 week prior to the due date.

Thanks!

Darren Mullen

@Brandee Pak Yes, that should be doable. The Only think you'd have to watch for is that you wouldn't want to due date to advance to the next due date once you reach the due date. That probably doesn't make much sense, so here is what I'm trying to say:

On 1/9 you want the notification to trigger based on that that date being reached, so you want to make sure that on 1/9 the due date stays at 1/9 to ensure the notification is triggered, then on 1/10 you can have the due date advance to 3/9.

This would be pretty simple as a column formula using a comparison to the Today() function. I suppose the complication could be around the year component of the date, but that's pretty easy to handle. Are the due dates the same for all years?

Brandee Pak

I can see the issue at hand.

I will have the notifications set to run 2 weeks prior to the due date and again 1 week prior to the due date if the the sum remains zero indicating no information has been added.

Nothing will actually automate on the due date so I suppose if if the date advanced to the next due date on the current due date, it would be okay.

Yes, the dates will remain the same despite the year.

Andrée Starå

Hi @Brandee Pak

I hope you're well and safe!

To add to Darren's excellent advice/answer.

Another option could be to add a so-called helper sheet (or somewhere in the current sheet) and have an INDEX/MATCH formula collect the correct dates.

Would that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Brandee Pak

I am not as familiar with INDEX/MATCH formulas. But when reading the help sheet on it, it makes me think that instead I could add due dates columns for each quarter and add the dates in into the ancestor zero row.

But then I think is there potential for me to make each sheet an ongoing sheet in which I don't have to build new columns for each quarter of FY24 and so on.

Any thoughts?

Thanks for helping me think through this!