Calculating Date for Same Day Each Month

jennclay

I'm trying to calculate the date for the 3rd Tuesday of each month. Is there a formula for that?

Paul Newcome

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, "")

jennclay

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)?

Paul Newcome

Make sure you are putting the formula into a date type column. For Tuesday, you would change each 5 to 3.

jennclay

OK great this worked! One tweak…I want the NEXT CONTACT date to display the 3rd Tuesday of the month following the month in the LAST CONTACT column.

Paul Newcome

In that case, replace each instance of

Date@row

with

IFERROR(DATE(YEAR([Last Contact]@row), MONTH([Last Contact]@row) + 1, 1), DATE(YEAR([Last Contact]@row) + 1, 1, 1))

jennclay

this is the formula I have now:

=IFERROR(DATE(YEAR([Last Contact]@row), MONTH([Last Contact]@row), 1) + (IF(WEEKDAY(DATE(YEAR([Last Contact]@row), MONTH([Last Contact]@row), 1)) <= 3, 3, 3 + 7) - WEEKDAY(DATE(YEAR([Last Contact]@row), MONTH([Last Contact]@row), 1))) + 14, "")

And this is what I'm seeing:

Last Contact Next Contact

02/19/25 02/18/25

But I want to see 03/18/25 in the Next Contact column (the 3rd Tuesday in in the month following the month in the Last Contact column)

Paul Newcome

Each place in the original formula that has "Date@row" should have been replaced with the entire IFERROR piece from my last comment.