Mirroring the most recent date to another cell

EricR

Greetings

I am attempting to complete a project where routine maintenance tasks are assigned in rotation between 3 shifts. Tasks are cycled Bi-Weekly, Monthly, Bi-Monthly and Bi-Yearly. I have a formula that is suppose grab the last date in a date column. The date that is being referenced in "BY1 Date" is applied by a workflow that "Records a Date" when the task is submitted via the attached form. The formula I'm using works in other scenarios (example of it working can be seen highlighted in yellow in "Date" column) but fails in this case as #INVALID DATA TYPE. "BY1 Data" and "BY Date" columns are indeed set to Date. Formula is:

=WORKDAY(INDEX([BY1 Date]:[BY1 Date], COUNTIFS([BY1 Date]:[BY1 Date], NOT(ISBLANK(@cell)))), -1)

I'm thinking it's because of the blank cells, but I'm having troubles figuring out the correct formula.

Any help provided would be greatly appreciated.

EricR

Welp, I may have been wildly overthinking this. I think I found my answer to be as simple as =MAX

Genevieve P.

Hey @EricR

I'm glad you found a solution! 🙂

You can use a combination of MAX(COLLECT if you need to filter down what type of Max date is needed. Here's an article with an example:

Formula combinations for cross sheet references

Cheers,

Genevieve