Creating Due Dates (Monthly & Quarterly)

Lloyd Langenhoven
Lloyd Langenhoven ✭✭
in Formulas and Functions

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.

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    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(Month@row), MONTH(Month@row), 1), [Workday Number]@row))

    Screen Shot 2020-12-03 at 6.19.11 PM.png

    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(Month@row), MONTH(Month@row), 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(Month@row), MONTH(Month@row), 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    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(Month@row) = 1, MONTH(Month@row) = 2, MONTH(Month@row) = 3), WORKDAY([Financial Quarter End]2, -[Workday Number]@row, Holidays2:Holidays11), IF(OR(MONTH(Month@row) = 4, MONTH(Month@row) = 5, MONTH(Month@row) = 6), WORKDAY([Financial Quarter End]3, -[Workday Number]@row, Holidays2:Holidays11), IF(OR(MONTH(Month@row) = 7, MONTH(Month@row) = 8, MONTH(Month@row) = 9), WORKDAY([Financial Quarter End]4, -[Workday Number]@row, Holidays2:Holidays11), IF(OR(MONTH(Month@row) = 10, MONTH(Month@row) = 11, MONTH(Month@row) = 12), WORKDAY([Financial Quarter End]5, -[Workday Number]@row, Holidays2:Holidays11))))), IF([Due Date Type]@row = "Monthly Task", WORKDAY(DATE(YEAR(Month@row), MONTH(Month@row), 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(Month@row), MONTH(Month@row), 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Lloyd Langenhoven
    Lloyd Langenhoven ✭✭
    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!

    · Share on FacebookShare on Twitter

Answers

  • Lloyd Langenhoven
    Lloyd Langenhoven ✭✭
    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.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    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(Month@row), MONTH(Month@row), 1), [Workday Number]@row))

    Screen Shot 2020-12-03 at 6.19.11 PM.png

    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(Month@row), MONTH(Month@row), 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(Month@row), MONTH(Month@row), 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Lloyd Langenhoven
    Lloyd Langenhoven ✭✭

    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!

    · Share on FacebookShare on Twitter
  • Lloyd Langenhoven
    Lloyd Langenhoven ✭✭

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


    image.png


    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?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    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(Month@row) = 1, MONTH(Month@row) = 2, MONTH(Month@row) = 3), WORKDAY([Financial Quarter End]2, -[Workday Number]@row, Holidays2:Holidays11), IF(OR(MONTH(Month@row) = 4, MONTH(Month@row) = 5, MONTH(Month@row) = 6), WORKDAY([Financial Quarter End]3, -[Workday Number]@row, Holidays2:Holidays11), IF(OR(MONTH(Month@row) = 7, MONTH(Month@row) = 8, MONTH(Month@row) = 9), WORKDAY([Financial Quarter End]4, -[Workday Number]@row, Holidays2:Holidays11), IF(OR(MONTH(Month@row) = 10, MONTH(Month@row) = 11, MONTH(Month@row) = 12), WORKDAY([Financial Quarter End]5, -[Workday Number]@row, Holidays2:Holidays11))))), IF([Due Date Type]@row = "Monthly Task", WORKDAY(DATE(YEAR(Month@row), MONTH(Month@row), 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(Month@row), MONTH(Month@row), 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Lloyd Langenhoven
    Lloyd Langenhoven ✭✭
    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!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    https://community.smartsheet.com/discussion/comment/272262#Comment_272262

    @Lloyd Langenhoven

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!