Getting a specific date of the month to show in a date field.
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.
Help Article Resources
Check out the Formula Handbook template!