Formulas to determine date

Part 1: Form - I have a form built to document monthly inspection results. Since I need to report results for each month on a scorecard, I have a question asking if the inspection is for the current month or the previous month. If current, they are asked for the inspection date. If, previous month, they are asked for the month of the inspection they are documenting. I built it like this in case they are documenting inspection results after the month's end - for example, documenting January's results in February.

Part 2: Form Data Sheet - I have set up helper columns to convert "January" to a month # and a parsed year based on the submission date.

I am looking for a way to come up with a "final walk-through date" to use for the monthly metrics. by pulling in "Date 1" if the inspection was for a previous date and pulling "Date 2" if they documented the current month inspection date.

Below is a screenshot of the data sheet. Thanks for reviewing!

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @Courtney Collier

In your example above, it appears you will have either one date or the other per row. If this is true then whether it is the current month or previous month is not needed.

This looks for a date in Date 2. If it's not there, it pulls in Date 1. If neither have a date, then it is pulling in the blank cell of Date 1, which will be ok as it will show as a blank Through Date.

=IF(ISDATE([Date 2 - Current]@row), [Date 2 - Current]@row,[Date 1 - Previous]@row)

Will this work for you?

Kelly

All comments

Kelly Moore

Hey @Courtney Collier

In your example above, it appears you will have either one date or the other per row. If this is true then whether it is the current month or previous month is not needed.

=IF(ISDATE([Date 2 - Current]@row), [Date 2 - Current]@row,[Date 1 - Previous]@row)

Will this work for you?

Kelly

Courtney Collier

@Kelly Moore Thank you, that worked! I was totally overthinking. Thanks for your help again :)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.