Mirroring the most recent date to another cell


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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!