Create a due date

Is it possible to create a due date for the first Thursday of the next month?

For example, I want to take a "date completed" and create a due date for the 3rd Thursday of the next month.

Answers

  • Humashankar
    Humashankar ✭✭✭✭✭

    Hi @Tracey Tume@Troy Bjelland

    Would you like to give a try with the below ADD function in the Due Date column and see if that helps:


    =ADD(A2, 3, "Thursday", 1, MONTH(A2), YEAR(A2))


    Ø ADD(A2, 3, "Thursday") - This part adds 3 weeks to the date in cell A2 and then ensures that the resulting date is a Thursday


    Ø 1, MONTH(A2), YEAR(A2)) - This part sets the month and year of the resulting date to be the next month and the same year as the "Date Completed" in cell A2


    With this you should calculate the due date as the 3rd Thursday of the next month based on the "Date Completed" provided in column A


    Hope this helps - Happy to help further!!

    Thank you very much and have a great one!

    Warm regards

    Huma

    Community Leader

  • I am getting an #UNPARSEABLE Error



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Troy Bjelland

    Try this. The formula looks intimidating but only because its long. To insure that the Thursday is next month's Thursday the formula has to calculate Day1 of the next month. It does this 4 times in the formula, which significantly increases its length.

    =DATE(YEAR([SCBA Inspection]@row), MONTH([SCBA Inspection]@row) + 1, 1) - DAY(DATE(YEAR([SCBA Inspection]@row), MONTH([SCBA Inspection]@row) + 1, 1)) + 22 - WEEKDAY(DATE(YEAR([SCBA Inspection]@row), MONTH([SCBA Inspection]@row) + 1, 1) - DAY(DATE(YEAR([SCBA Inspection]@row), MONTH([SCBA Inspection]@row) + 1, 1)) - 4)

    Will this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!