Help creating formula to return a date in a column that is closest to today's date

Siobhan16

I need help creating a formula that references the "Due in Payroll" column I have which is a series of dates. I would like the formula to look at that date column, and return whichever date in that column is closest to today's date.

I've tried several formulas, all come up invalid column type, unparseable invalid operation, etc.

Here's a few that the AI generation recommended that don't work.

=INDEX([Due in Payroll]:[Due in Payroll], MATCH(MIN(ABS([Due in Payroll]:[Due in Payroll] - TODAY())), ABS([Due in Payroll]:[Due in Payroll] - TODAY()), 0))

=INDEX(COLLECT([Due in Payroll]:[Due in Payroll], ISDATE(@cell ), true), MATCH(MIN(ABS([Due in Payroll]:[Due in Payroll] - TODAY())), ABS([Due in Payroll]:[Due in Payroll] - TODAY()), 0))

Jgorsich

There is probably a more elegant way to do it, but a helper column that just calculates your days between the due date and today would then be easier to run that index and match on, then you wouldn't need to nest the functions and it might avoid an unparseable error.