Creating Due Dates (Monthly & Quarterly)

Lloyd Langenhoven
Lloyd Langenhoven ✭
inFormulas 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 ofeachmonth, 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 Facebook Share on Twitter

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    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))

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

    Then in your full formula, including the quarterly instruction:

    =如果([到期日期类型]@row =“金融季度”,如果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

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    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:

    =如果([到期日期类型]@row =“金融季度”,如果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 asone working dayafter the initial date.

    ex:

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

    Then if you have1in your [Workday Number]@row cell, this says

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

    You can see this spelled out in theSyntax section, here.

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


    Let me know if you have any additional questions!

    Cheers,

    Genevieve

    · Share on Facebook Share 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 Facebook Share on Twitter

Answers

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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:<\/p>

=IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>

If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p>

=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>

In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106869,"type":"question","name":"Formula to Assign Symbol based on a cell's value","excerpt":"This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…","categoryID":322,"dateInserted":"2023-06-24T20:02:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-25T22:29:54+00:00","insertUserID":158092,"insertUser":{"userID":158092,"name":"MeganJF","url":"https:\/\/community.smartsheet.com\/profile\/MeganJF","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DgFow9e-1vo!YfMmmJOUTjo!UDpVBcB6ikY","dateLastActive":"2023-06-25T17:43:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":30,"score":null,"hot":3375372134,"url":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106869,"commentID":382193,"name":"Re: Formula to Assign Symbol based on a cell's value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382193#Comment_382193","dateInserted":"2023-06-25T22:29:54+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-25T17:10:13+00:00","dateAnswered":"2023-06-25T03:32:30+00:00","acceptedAnswers":[{"commentID":382165,"body":"

Hi @MeganJF<\/p>

I guess you forget to use AND( ).<\/p>

For example, <\/p>