Find Next Closest Date after Specified Date

[robk94]

Hi There,

I have looked through the community and have found very similar queries but not the exact solution I am looking for, hoping you may be able to help and apologies if this has been answered before.

My business example is this: I have "Employee 1" and the start date for the period (column B). I also have the end date (column E).

On another input I then have a list of the dates they took leave (not necessarily all falling into the period so a simple first and last cross tab tool won't work).

What I want to achieve is in column C bring in the next date they took leave from that list AFTER the start date.

Similarly in column D I want to bring in the last date they took leave BEFORE their end date.

I hope I have explained this well and thanks in advance for any suggestions!

[DavidP]

This is how I would do it

[robk94]

Thank you for the reply David.

The example I gave works with your solution having just one employee. If I incorporate this into a sheet which has multiple employees I run into issues. Any suggestions? Apologies I only gave the sample of one employee but I hope to incorporate the solution into a larger data set with multiple employees and dates.

[DavidP · Accepted solution]

In that case you would add Employee as a field in the leave days data set and use a join tool that joins the 2 data sets on Employee ID, updated example updated.

[BenMoss]

Hi @robk94 ,

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!