Relative Dates
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.
Answers
-
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...
share improve this question follow
25k17
17 gold badges
64
64 silver badges
97
97 bronze badges
asked Nov 20 '11 at 22:57
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
3 Answers
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
- 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!