Relative Dates

Options

I am trying to duplicate the formulas in another app that uses dates like: every Tuesday, the 3rd Thursday of the month, etc as due dates instead of specific dates.

Tags:

Answers

  • Stephen Smith
    Options

    I found these formulas for Excel will they work in Smartsheet?

    11


    5


    What Excel formula can I use to calculate the second Monday of a given month?

    You can assume I have a cell containing the first day of the month to work with.

    In the interests of other users, can you please also explain how to alter the formula to get the 1st, 3rd or 4th Monday of the month too, and other days of the week too. eg the third Friday of the Monday...

    microsoft-excel

    worksheet-function

    date-time

    share  improve this question  follow 

    edited Mar 27 '16 at 20:16


    fixer1234

    25k17

    17 gold badges


    64

    64 silver badges


    97

    97 bronze badges

    asked Nov 20 '11 at 22:57


    Highly Irregular

    2,52317

    17 gold badges


    35

    35 silver badges


    50

    50 bronze badges

    • I wanted a formula to compute the second Wednesday of the current month and year to use to automatically add the date of the meeting to a sign in sheet for a monthly meeting. I created the formula below. =DATE(YEAR(NOW()),MONTH(NOW()),1+7*2)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),8-4)) – user554112 Feb 4 '16 at 18:44 

    add a comment

    3 Answers

    Active

    Oldest

    Votes


    19




    Here's something I found with a quick search:

    Generically you can get the nth xday of the month with this formula

    =DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday)) 
    

    where year is in B2 and month (as a number 1 to 12) is in A2, and where xday is a number

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!