Getting a specific date of the month to show in a date field.

Dustin AK Lean VA

A quick background of the build. I am building a sheet that show an "Activity" and its "Date to be Completed by", along with other columns but for this question they don't affect anything. I have a bunch of formulas to get specifics days of the week or to have a task reset its date to the next day and so on.

Currently I am working on dates for monthly task. The majority of them are just due on a specific date each month so I am just using this formula: "=DATE(YEAR(TODAY()), MONTH(TODAY()), 7)". So that is always shows the current year and month, and in this case the 7th of the month. Very simple. What I am having trouble with though is a few task are due by the 3rd Wednesday of the month, or by the 1st Monday of the month.

I have tried different things with "=DATE(YEAR(TODAY()), MONTH(TODAY())," and then using WORKDAY without any luck though. And searching other similar questions like mine but most of them were for a calculation dates for another cell. I just want the date its due to pop in "date to be completed by" that would be the 3rd Wednesday of the month if this is possible.

Jeff Reisman

@Dustin AK Lean VA It may be easier to do this with a lookup.

Take 10 minutes and create a lookup sheet, with columns "Year" (Text/Number,) "Month" (Text/Number,) "FirstMonday" (Date,) and "ThirdWed" (Date.) Populate the Year column with the 4-digit year, the month column with 1 through 12, and the dates for the first Monday and 3rd Wednesday of each month.

Then it's just a matter of using in INDEX/COLLECT to grab the dates that meet your criteria. For instance if you have a task start date of July 17th 2023 and it's due the 3rd Wednesday in August:

=INDEX(COLLECT({Lookup Sheet 3rd Wed col}, {Lookup Sheet Year}, YEAR([Start Date]@row), {Lookup Sheet Month}, (MONTH([Start Date]@row) + 1)), 1)

In English: Collect the date value in the lookup sheet ThirdWed column where the Year is the same as this row's start date and the month equals the start date's month plus one.

If needed, you can through in an IF that accounts for changes in the year, like if the Start Date is in December, add one to the year instead and set the month criteria to "1".

Dustin AK Lean VA

Thank you for this, I like this a lot but, it wont do exactly what I am after. It did give me the direction I needed though to find a solution for what I was after. Thank you!!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/384605#Comment_384605

Good deal!

Feel free to share your solution in this thread so that others may find it later when looking to solve similar challenges.

Leibel S

@Dustin AK Lean VA

Try the below, this one would give you the 3rd Wednesday of the current month.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8 - 4))

to change this update the bolded numbers.

the first one is which number you want (how many weeks in) and the second is the day of the week.

the below formula was changed to match the 1st Monday of the current month

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1 + (7 * 1)) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8 - 2))

Dustin AK Lean VA

@Leibel S

Oh, this is exactly what I was trying to do thank you!

What I ended up doing was making a separate page like @Jeff Reisman suggested and I just used an INDEX formula to pull the top date from that sheet onto the main sheet for the due date, and used an automation to move the top row when the date was in the past. So that the next date would move up and show in the field with the INDEX formula so that it will automatically update when a date is past if that makes any sense lol.

Your formula will make it so that they don't have to update a separate sheet that holds the dates needed, but it wont update the date until the next month starts. So I will just see which one they like better.

Paul Newcome

@Leibel S's formula can be modified to shift to next month if the date this month has already passed.

=IF(TODAY()<= DATE(YEAR(TODAY()), MONTH(TODAY()), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8 - 4)), DATE(YEAR(TODAY()), MONTH(TODAY()), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8 - 4)), DATE(YEAR(TODAY()) + IF(MONTH(TODAY()) = 12, 1, 0), IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(TODAY()) + IF(MONTH(TODAY()) = 12, 1, 0), IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), 8 - 4)))

I believe the above should work. I just made some quick edits here without testing, so @Leibel S and @Jeff Reisman may want to double check my syntax.

Dustin AK Lean VA

@Paul Newcome

I just tested it out changing the numbers to get the exact days I needed and it seems to be working. Thank you!

Dustin AK Lean VA

@Paul Newcome @Leibel S @Jeff Reisman

I do have a follow up that would be along the same lines are this date formula. I am using this right now "=TODAY() + (6 - WEEKDAY(TODAY()))" for weekly task. So that it shows that this task is due on Fridays of the current week. I am not sure how this one will update or even if it will, I was assuming these would update when the next week starts but I haven't been able to test that and was waiting until Sunday to see. But I would like it to update after the current Friday it was on passes and then it would show it for the next coming Friday, if that makes sense. After seeing that last formula though it makes me wonder if I can do that same thing with this formula.

Paul Newcome

Happy to help. 👍️

You SHOULD be able to use similar logic. I'll try to simplify the logic a bit so that you can leverage it as needed.

Basically you use an IF statement to say that if TODAY is less than or equal to output_date, then output_date. Otherwise (meaning today has passed it) next_output_date.

In the formula I provided, I added a month (had to add a few extra pieces overall to accommodate going into a new year) to the base formula and then used:

=IF(TODAY()<= output_date, output_date, output_date_+_1_month)

Weekly would be the same idea but adding 7 days (and not have to worry about anything special for rolling into next year).

=IF(TODAY()<= output_date, output_date, output_date_+_7_days)

Troy Bjelland

How can this be applied to a due date? I want to take a "date completed" and create a due date for the 3rd Thursday of the next month.