Get the Relative Date From Prior Year

Hello all,
I have 2 tables 2022 and 2023 each set up with the same columns

The Date column contains duplicates, this formula gives me the total unique days

Please Login or Register  to view this content.

How do I do the reverse, for let's say the 83rd Production day in 2023 (from the the 2023 table it is 23-Apr-2023), how do I get the date of the 83rd day in the 2022 table. Not using the 83rd calendar day because if no work is done on certain days, those days won't count towards the actual work days. I want to get the 83rd date from the array.


Edit: Attached Workbook
TEST.xlsx

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Only checking from the 1st of January for each year:

Removing all the Date duplicates from PROD2022, the 79th date is 25-Apr-2022

For PROD2023 the 79th date is 23-Apr-2023


The 18th Date in PROD2023 is 26-Jan-2023

The 18th Date in PROD2022 is 25-Jan-2022

These are the results I'm looking for.

I will ask the question a second time: is this what you want to achieve?

the dates in both tables are sorted ascending?

I'm not follow your logic how you get the result from post#12 ?
could you explain it step by step?

eighteenth position from distinct dates in PROD2023 is 28/03/2023 (without sorting!)
that is why I asked about explaining your logic how do you calculate the eighteenth position
You don't explain, you just repeat the same thing over and over again
I cannot help with formula but I can do that via Power Query
i can show from both tables the date in 18th position at the same time

after sort asc both tables 18th position is

20/01/2022

18

20/01/2023

18

I extracted the unique dates from each table. I see the count is returning different dates

Edit:
2022.jpg

2023.jpg

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.