Forum Discussion

kmtiburona's avatar
kmtiburona
Copper Contributor
Jan 23, 2025

Sequence Formula for Deadline Calculations

Good afternoon.

I have been tasked with calculating the deadline dates for start dates on rolling courses. I am currently working to develop the best system. Currently, multi people have been manually type in/copy/paste bulk data. The deadline spreadsheet for example has 15 different tabs on it. 

There are a few quirks here. For example, our December start date is always longer, due to the holiday/break schedule.

I have included a brief data set, including the sequence formula I used to calculate the deadline. Some of the program courses last 3 weeks, some 6 weeks, others can be 9 - 11 weeks. I am intending to try the sequence formula in excel to see if I can develop a formula for this process to be more automated and easily calculate, without error. I am looking for input/thoughts/reference to understand the sequence function to aide with my query or any other formula suggestions.

Is there a better way to semi-automate this process? Where I can complete "shells' or "templates" and pull (either from an excel formula or simple copy/paste). I am thinking since there are 15 tabs, I can set it up to pull from other tabs.

There is a blank line in between the different class starts intentionally. This is formatted specifically to read easier.

This is a rolling list, so new start dates will be added at the bottom, and then we need to calculate and publish the deadlines as the new start dates are added at the bottom. So on the Full-Time data set, you can see where new Start Dates have been added. When new start dates are added, I need to find an easy way to go in and add the deadlines.

The deadline is based on the last day of the calls. So English 1 had its last week on December 30, 2024. This means January 3, 2025 is the last day of that class. The deadline will always be the last day of class, unless the issue occurs during the last week of class. Then, students will get an extra 7 days. The last week will always have a different deadline.

Typically, we publish the class start dates for 1 - 3 years in advance. As this gets updated, the deadline database must also get updated to add the new class start dates and then determine and publish the deadlines associated.

I appreciate any thoughts/suggestions when it comes to formulas or different patterns that may develop better formulas than I have. I hope the information I provided was helpful and clear. I tried to provide informational knowledge as needed. Please let me know if there is anything I can clarify to assist better.

Thank you all in advance:

Sample Data Set with Class Start Week Of Date, Class & Deadline & Formula used to calculate.

 

 

 

Warm regards,

Kristina

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    I'm not sure I understand all that but maybe:

    =let(maxD, MAXIFS(G:G,H:H,H27), maxD+4+7*(maxD=G27))

    basically this will find the max date from column G that has the same class name as "this" row and then add 4 to that and then add another 7 if "this" row is that max date

Resources