Creating Due Dates (Monthly & Quarterly)

Lloyd Langenhoven

Greetings

I have been asked by a colleague to build a smartsheet that sets out a list of tasks to be completed and the due date of each task (to be used primarily in calendar view by the colleague to plan her time).

Her due date for a task is either made with reference to:

(i) a business day in a particular month (i.e. task A is due on the 15th business day of each month, which for this month would be 22 December 2020); or

(ii) a business day occurring x business days from the end of a financial quarter (i.e. task B is due 10 days prior to the end of the financial quarter or is due by the 20th business day of the financial quarter).

In some cases she requires a third formulation to determine a due date, which is usually like " due by the 3rd business day of month 3" (i.e. - the 3rd business day of March).

I have played around with and used the smartsheet formula examples regarding dates and just cannot fathom how it would be possible for me to create something that can cater for these nuanced dates and date ranges. Is this something that Smartsheet can even do?

I am a legally trained professional and this is an area of knowledge that I am severely lacking in and any help would be appreciated.

Genevieve P.

Hi @LloydGroup

How are each of these rows identified for when they are each type of due date? We can build multiple, separate formulas for each scenario, but the key factor will be how you tell the cell which formula to use/populate.

I've gone through two different formulas below, then at the end added them together in an IF statment.

(i) 15th business day of each month

This formula finds the work day that's 15 days in Today's Year and Today's Month:

=WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 15)

But if the current row needed to change each month to the 5th workday, it would be as follows:

=WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 5)

So then, how do you know what day of the month it's supposed to be? If there's a column in your sheet that says this (ex. a cell that just contains 15 as a number), then you can reference the cell in your formula instead of the number:

=WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Workday Number]@row)

Some Help Articles I used for this formula:

• WORKDAY function
• @row function
• DATE function / YEAR function / MONTH function
• TODAY function

(ii) a business day occurring x business days from the end of a financial quarter

In this scenario, you will still need the Number of days before or after the current Quarter to be present in the sheet for each row's specific use-case.

But, you can use a Nested IF statement to check today's current month (1 = Jan, 2 = Feb, etc). Then based on this current month, find a specific Work Day before the end of the Quarter.

For Quarter 1:

=IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), WORKDAY(DATE(2021, 03, 31), - ([Workday Number]@row)),

^ here, if Today's month is Jan, Feb, or March, it will find the WORKDAY that references the DATE input (the Q1 end date, March 31st or DATE(2021, 03, 31)) and minuses off of this the number of days you want to have it due before then using - ([Workday Number]@row)

You could also have a date cell in your sheet that identifies the Financial End date somewhere, and then reference this instead of typing out DATE(2021, 03, 31), like so: [Financial Quarter End]$8

Full Formula example with all quarters:

=IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), WORKDAY([Financial Quarter End]$8, -([Workday Number]@row)), IF(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6), WORKDAY([Financial Quarter End]$9, -([Workday Number]@row)), IF(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), WORKDAY([Financial Quarter End]$10, -([Workday Number]@row)), IF(OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12), WORKDAY([Financial Quarter End]$11, -([Workday Number]@row))))))

Some Help Articles I used in this formula:

• IF Statement
• OR function
• Reference a cell with Absolute References ($)

Combining Both Formulas:

Now if you wanted the cell to know WHICH due date to search for (quarter or monthly), you would need to have that indication in the sheet somewhere. I have it on the very left, saying either "Monthly Task" or "Financial Quarter".

Then you can embed these two different statements into an IF statement which looks at this helper column and produces one of the two formulas:

=IF([Due Date Type]@row = "Financial Quarter", Formula, IF([Due Date Type]@row = "Monthly Task", Other Formula))

FULL FORMULA:

=IF([Due Date Type]@row = "Financial Quarter", IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), WORKDAY([Financial Quarter End]$6, -([Workday Number]@row)), IF(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6), WORKDAY([Financial Quarter End]$7, -([Workday Number]@row)), IF(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), WORKDAY([Financial Quarter End]$8, -([Workday Number]@row)), IF(OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12), WORKDAY([Financial Quarter End]$9, -([Workday Number]@row)))))), IF([Due Date Type]@row = "Monthly Task", WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Workday Number]@row)))

Does this make sense? Let me know if you need help understanding any portion of this and I'm happy to go into more detail.

Cheers,

Genevieve