Calculating Date for Same Day Each Month
I'm trying to calculate the date for the 3rd Tuesday of each month. Is there a formula for that?
Answers
-
You would insert a date type column (called "Date" in the formula below) and on each row select a date from the month you want the 3rd Thursday from. It doesn't matter which day in the month you choose so long as the month and year you want are correct. Then the third Thursday would be found by using this column formula:
=IFERROR(DATE(YEAR(Date@row), MONTH(Date@row), 1) + (IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))) + 14, "")
-
Hi Paul
It returned INVALID COLUMN VALUE. The column names I have are:
[Last Contact] (which I renamed to DATE) to indicate the date from which I'm calculating the next 3rd Tuesday
[Next Contact] which is where I want the date of the 3rd Tuesday to go eventually
[3rd Tuesday] as a test column where I've placed your formula to test it before replacing the contents of [Next Contact]
And do i need to make any adjustments to the formula to return 3rd TUESDAY (not Thursday)?
-
Make sure you are putting the formula into a date type column. For Tuesday, you would change each 5 to 3.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!