Relative Dates

Stephen Smith

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.

Stephen Smith

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