Formulas to determine date

Courtney Collier

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!

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

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

Courtney Collier

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