Creating Due Dates (Monthly & Quarterly)

12/01/20
Accepted

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.

Best Answers

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @Lloyd Langenhoven

    You can change TODAY to reference a specific date, however then you would need to have that date listed somewhere in the row for it to know which month this specific task requires... like how we have the Type of due date (monthly or quarterly, etc).

    So, instead of TODAY, you could have a "month" date column set up:

    =IF([Due Date Type]@row = "Monthly Task", WORKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1), [Workday Number]@row))

    Then in your full formula, including the quarterly instruction:

    =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([email protected]), MONTH([email protected]), 1), [Workday Number]@row)))


    In regards to excluding other holiday dates, no problem! This is part of how the WORKDAY function reviews information, see here:

    WORKDAY(date, num_days, [holidays])

    It's optional, but at the end of your workday function you can add a range that contains a list of your other dates that should be recognized as holidays.

    Ex:

    WORKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1), [Workday Number]@row, [Holiday Range]$1:[Holiday Rage]$25)

    You would need to add in this range every time there's WORKDAY listed in the formula. Let me know if you need help adding this in!

    Cheers,

    Genevieve

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @Lloyd Langenhoven

    No problem, happy to help again!

    For your first issue when adding in the Holidays, it looks like you just have some extra parentheses in there that are confusing the WORKDAY function and causing the error.

    Try this:

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


    In regards to your second question, WORKDAY counts a number of days from a specific date to return the working day from that count. This means that since we've put Feb 1st as your date to count from, if you use 1 as the number of days to add on, then it will return Feb 2nd as one working day after the initial date.

    ex:

    WORKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1), [Workday Number]@row, Holidays2:Holidays11)))

    Then if you have 1 in your [Workday Number]@row cell, this says

    WORKDAY(Feb 1st, + 1day, excluding holidays)

    You can see this spelled out in the Syntax section, here.

    To have it be Feb 1st, put a 0 in the [Workday Number]@row cell.


    Let me know if you have any additional questions! 🙂

    Cheers,

    Genevieve

  • Accepted Answer

    @Genevieve P - you (and smartsheet) have been a life saver. I cannot thank you enough for your effort and assistance. All is in order now and I am sure that my colleague will be over the moon with the final result!

Answers

  • edited 12/03/20

    Hi @Genevieve P


    Thank you so much for your time and help with this - this is definitely very helpful and has almost entirely solved my issue! 😁

    However, would it be possible in the final formula to change the references to "TODAY()" to some other point? For example, the formula is currently able to provide me with all the relevant dates in respect of December 2020 but not in respect of April 2021 (as that is not TODAY). My understanding is that it would require me to wait until April 2021 actually begins before the formula can guide me in respect of April 2021.

    Ideally, I would want to be able to use the formula to be able to get the relevant dates for the whole of 2021 at the start. Is it possible for this to be incorporated into the formula in any way?

    You have already been of great assistance - thank you!

    EDIT: Would it be possible to exclude a pre-defined list of holidays in the formula (so as to not count public holidays as business day)? I already have the list of 2021 public holidays for the jurisdiction in question.

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @Lloyd Langenhoven

    You can change TODAY to reference a specific date, however then you would need to have that date listed somewhere in the row for it to know which month this specific task requires... like how we have the Type of due date (monthly or quarterly, etc).

    So, instead of TODAY, you could have a "month" date column set up:

    =IF([Due Date Type]@row = "Monthly Task", WORKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1), [Workday Number]@row))

    Then in your full formula, including the quarterly instruction:

    =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([email protected]), MONTH([email protected]), 1), [Workday Number]@row)))


    In regards to excluding other holiday dates, no problem! This is part of how the WORKDAY function reviews information, see here:

    WORKDAY(date, num_days, [holidays])

    It's optional, but at the end of your workday function you can add a range that contains a list of your other dates that should be recognized as holidays.

    Ex:

    WORKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1), [Workday Number]@row, [Holiday Range]$1:[Holiday Rage]$25)

    You would need to add in this range every time there's WORKDAY listed in the formula. Let me know if you need help adding this in!

    Cheers,

    Genevieve

  • Your additional assistance is much appreciated, @Genevieve P ! I will test this ASAP and let you know if it has cleaned it all up - you (and smartsheet) are a life saver!

  • Hi again @Genevieve P


    I am almost 100% there with the sheet - thank you!


    However, I just cannot seem to get the formula to exclude my holidays (housed in a column entitled "Holidays"). I continuously get an #UNPARSEABLE error - below is a screen shot of what it looks like as well as the formula I am currently using:


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



    And one final question from me - when calculating the due date (either quarterly or monthly) does smartsheet add 1 day to the formula? The reason that I ask is because I have entered a workday period of 1 day (i.e. - the task is due on the first business day of February 2021 (Monday 1 February 2021)) yet the resulting due date I get is 02/02/21. I would expect the due date result to be 01/02/21 - is there something that I am missing here?

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @Lloyd Langenhoven

    No problem, happy to help again!

    For your first issue when adding in the Holidays, it looks like you just have some extra parentheses in there that are confusing the WORKDAY function and causing the error.

    Try this:

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


    In regards to your second question, WORKDAY counts a number of days from a specific date to return the working day from that count. This means that since we've put Feb 1st as your date to count from, if you use 1 as the number of days to add on, then it will return Feb 2nd as one working day after the initial date.

    ex:

    WORKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1), [Workday Number]@row, Holidays2:Holidays11)))

    Then if you have 1 in your [Workday Number]@row cell, this says

    WORKDAY(Feb 1st, + 1day, excluding holidays)

    You can see this spelled out in the Syntax section, here.

    To have it be Feb 1st, put a 0 in the [Workday Number]@row cell.


    Let me know if you have any additional questions! 🙂

    Cheers,

    Genevieve

  • Accepted Answer

    @Genevieve P - you (and smartsheet) have been a life saver. I cannot thank you enough for your effort and assistance. All is in order now and I am sure that my colleague will be over the moon with the final result!

  • @Lloyd Langenhoven

    It's been my pleasure! I'm so glad you were able to set up your sheet as needed.

Sign In or Register to comment.