Formulas to Add Meeting Date and then Corresponding Due Dates?

My company has a regular meeting with a client, with required notes due 3 days later, for several years. I am looking for formulas that will help me do this efficiently, but I haven't found answers with searches.

1) Is there a formula/function to find the last Monday in a month? This info would go on the monthly meeting line, eg "January Meeting." (Current dates shown were manually added.)

2) Can I use a formula/method to enter the start date and calculate the due date of for the notes, eg "January Notes?" Below is the current smartsheet - I can edit anything to make this flow better. I will be using similar features for other projects.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Genevieve P.

Hi @Kaplan123_

2) Your second formula is much easier, so lets start with that! 🙂

If you have a Start Date column with a date, you can use WORKDAY() to add a number of days to that date and find an End Date:

=WORKDAY([Projected Start Date]@row, 3)

Since you know it's always 3 days, you can simply add that as the number at the end of your formula.

1) We can use formulas for this, but it gets a little complicated, due to the variables of Year, Month, and then based on the month, the End Day.

The way I would personally do it is to add 2 helper columns. One to grab the Month as a number based on the text value in your Description Column, and one to surface the last Day of that month, like so:

=IF(CONTAINS("January", Description@row), 1, IF(CONTAINS("February", Description@row), 2, IF(CONTAINS("March", Description@row), 3, IF(CONTAINS("April", Description@row), 4, IF(CONTAINS("May", Description@row), 5, IF(CONTAINS("June", Description@row), 6, IF(CONTAINS("July", Description@row), 7, IF(CONTAINS("August", Description@row), 8, IF(CONTAINS("September", Description@row), 9, IF(CONTAINS("October", Description@row), 10, IF(CONTAINS("November", Description@row), 11, IF(CONTAINS("December", Description@row), 12))))))))))))

Then with the Month surfaced, we can use a formula in the "Last Day" column like so:

=IF(Month@row = "", "", IF(OR(Month@row = 1, Month@row = 3, Month@row = 5, Month@row = 7, Month@row = 8, Month@row = 10, Month@row = 12), 31, IF(Month@row = 2, 28, 30)))

Since these have both been returned as numbers, this means we can use them in a DATE function to return the Month and End Day, and use the Year from your Parent row.

We can use these to see if that end date of the month is the Monday:

=IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 2,

If it is, we simply return that same date. If it's not, we need to check each of the days back one from this date:

=IF(CONTAINS("Meeting", Description@row), IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 2, DATE(PARENT(Description@row), Month@row, [Last Day]@row), IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 3, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 1, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 4, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 2, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 5, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 3, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 6, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 4, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 7, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 5, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 1, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 6))))))))

This formula is looking for "Meeting" in the Description, but you may also want to include in the "Notes" rows, so that you can use the Workday formula for the due date:

=IF(OR(CONTAINS("Meeting", Description@row), CONTAINS("Notes", Description@row)), IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 2, DATE(PARENT(Description@row), Month@row, [Last Day]@row), IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 3, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 1, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 4, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 2, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 5, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 3, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 6, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 4, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 7, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 5, IF(WEEKDAY(DATE(PARENT(Description@row), Month@row, [Last Day]@row)) = 1, DATE(PARENT(Description@row), Month@row, [Last Day]@row) - 6))))))))

I hope this helps!

Genevieve

Kaplan123_

Hi Genevieve,

Thanks so much for this detailed reply. I'm going to work on this and let you know how it goes.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.